STMulti — Function that returns @p_geometry as a MULTI* geometry

Function Specification

Function [dbo].[STMulti] (
@p_geometry geometry
)
Returns geometry

Description

In situations where a geometry column is constrained to hold only the multi version of a base geometry, storage of a single geometry eg LineString requires that geometry to be wrapped as a multi geometry eg MultiLineString.

ALTER TABLE [dbo].[Colony] WITH CHECK
ADD CONSTRAINT [Colony_Geom_Geometry_Type_Ck]
CHECK ([geom] is null OR [geom].[STGeometryType]()=’MultiPoint’);

So, @p_geometry is returned as a MULTI* geometry, e.g., POINT -> MULTIPOINT

If @p_geometry is already a MULTI*, it is returned unchanged e.g., MULTIPOINT -> MULTIPOINT

STMulti, where is wraps the single geometry returns a multi-geometry that contains only 1 geometry, so the SQL Server Spatial function STNumGeometries will return 1.

Parameters

@p_geometry (geometry) - Supplied geometry of a supported type.

Result

This function always returns a multi-geometry type object.

Notes

A CircularString input geometry is returned as a GEOMETRYCOLLECTION as it cannot be returned as a MUTLICURVE as it is not instantiable.

Additionally, one cannot construct a MULTILINESTRING containing it MUTLILINESTRING(CIRCULARSTRING(….)).

CompoundCurves are not supported because MultiCurve is not instantiable in SQL Server Spatial

Examples

USE GISDB -- Change to your own database.
GO
SELECT f.mGeom.AsTextZM() as mGeom, f.mGeom.STNumGeometries() as numGeometries
FROM (SELECT [dbo].[STMulti](geometry::STPointFromText('POINT(0 0)',0)) as mGeom
UNION ALL
SELECT [dbo].[STMulti](geometry::STGeomFromText ('POLYGON ((0 0,10 0,10 10,0 10,0 0))',0)) as mgeom
UNION ALL
SELECT [dbo].[STMulti](geometry::STGeomFromText ('LINESTRING(0 0,10 10,20 20)',0)) as mgeom
UNION ALL
SELECT [dbo].[STMulti](geometry::STGeomFromText('CIRCULARSTRING(9.962 -0.872,10.1 0,9.962 0.872)',0)) as mgeom
) as f;
GO
mGeom numGeometries
MULTIPOINT ((0 0)) 1
MULTIPOLYGON (((0 0, 10 0, 10 10, 0 10, 0 0))) 1
MULTILINESTRING ((0 0, 10 10, 20 20)) 1
GEOMETRYCOLLECTION (CIRCULARSTRING (9.962 -0.872, 10.1 0, 9.962 0.872)) 1

STRound — Function which rounds the ordinates of the supplied geometry.

Function Specification

Function [dbo].[STRound] (
@p_geometry geometry,
@p_round_xy int = 3,
@p_round_zm int = 2
)
Returns geometry

Description

The result of many geoprocessing operations in any spatial type can be geometries whose ordinates (X, Y etc) have far more decimal digits of precision than the initial geometry.

Additionally, some input GIS formats, such as shapefiles (which has no associated precision model), when loaded, can show far more decimal digits of precision in the created ordinates misrepresenting the actual accuracy of the data.

STRound takes a geometry object and some specifications of the precision of any X, Y, Z or M ordinates, applies those specifications to the geometry and returns the corrected geometry.

The @p_round_xy/@p_round_zm values are decimal digits of precision, which are used in TSQL’s ROUND function to round each ordinate value.

Parameters

@p_geometry (geometry) - supplied geometry of any type.
@p_round_xy (int) - Decimal degrees of precision to which calculated ordinates are rounded.
@p_round_zm (int) - Decimal degrees of precision to which calculated ordinates are rounded.

Result

This function accesses all ordinates in the supplied geometry, rounding all ordinates using the supplied parameter values.

Examples

-- Geometry
-- Point
SELECT [dbo].[STRound](geometry::STPointFromText('POINT(0.345 0.282)',0),1,1).STAsText() as RoundGeom
UNION ALL
-- MultiPoint
SELECT [dbo].[STRound](geometry::STGeomFromText('MULTIPOINT((100.12223 100.345456),(388.839 499.40400))',0),3,1).STAsText() as RoundGeom
UNION ALL
-- Linestring
SELECT [dbo].[STRound](geometry::STGeomFromText('LINESTRING(0.1 0.2,1.4 45.2)',0),2,1).STAsText() as RoundGeom
UNION ALL
-- LinestringZ
SELECT [dbo].[STRound](geometry::STGeomFromText('LINESTRING(0.1 0.2 0.312,1.4 45.2 1.5738)',0),2,1).AsTextZM() as RoundGeom
UNION ALL
-- Polygon
SELECT [dbo].[STRound](geometry::STGeomFromText('POLYGON((0 0,10 0,10 10,0 10,0 0))',0),2,1).STAsText() as RoundGeom
UNION ALL
-- MultiPolygon
SELECT [dbo].[STRound](
geometry::STGeomFromText('MULTIPOLYGON (((160 400, 200.00000000000088 400.00000000000045, 200.00000000000088 480.00000000000017, 160 480, 160 400)), ((100 200, 180.00000000000119 300.0000000000008, 100 300, 100 200)))',0),
2,1).STAsText() as RoundGeom
RoundGeom
POINT (0.3 0.3)
MULTIPOINT ((100.122 100.345), (388.839 499.404))
LINESTRING (0.1 0.2, 1.4 45.2)
LINESTRING (0.1 0.2 0.3, 1.4 45.2 1.6)
POLYGON ((0 0, 10 0, 10 10, 0 10, 0 0))
MULTIPOLYGON (((160 400, 200 400, 200 480, 160 480, 160 400)), ((100 200, 180 300, 100 300, 100 200)))
-- Geography
-- Can't overload existing STRound so have to use conversion functions.
SELECT [dbo].STToGeography(
[dbo].[STRound](
[dbo].STToGeometry(
geography::STGeomFromText('LINESTRING(141.29384764892390 -43.93834736282 234.82756,
141.93488793487934 -44.02323872332 235.26384)',
4326),
4326
),
7,
3
),
4326
).AsTextZM() as rGeom;
rGeom
LINESTRING (141.2938476 -43.9383474 234.828, 141.9348879 -44.0232387 235.264)

Using PostgreSQL’s XML processing to load spatial data

Spatial data comes in many forms, but the most common is, probably, XML.

If you have a need to load XML data – whether that be GML or other XML with spatial data – you may not know it, but you can use native PostgreSQL tools to load the XML data along with its geometry data.

Does this mean we no longer need traditional spatial/GIS tools such as FME or ogr2ogr?

Absolutely not!

It is just that in certain circumstances, the only tool in your tool-box just might be ordinary old XML type processing.

There are two scenarios

1. Loading WKT as part of a simple XML document

Here is a snipped of XML data that contains WKT data:

Normally one would use the COPY command in psql or pgAdmin IV to load an XML file from the file system into a table. But for the first part of article, we will use the XML directly.

The following SQL Select statement uses PostgreSQL’s XML processing capability to access the XML and extract the hotel information including accessing the WKT and converting it to an geography object.

With hoteldata as (
SELECT
'<hotels>
<hotel id="mancha">
<name>La Mancha</name>
<location srid="4326"><strong>POINT(-43.23737 147.14756)</strong></location>
<rooms>
<room id="201"><capacity>3</capacity><comment>Great view of the Channel</comment></room>
<room id="202"><capacity>5</capacity></room>
</rooms>
</hotel>
<hotel id="valpo">
<name>Valparaíso</name>
<location srid="4326"><strong>POINT(-43.26737 147.29756)</strong></location>
<rooms>
<room id="201"><capacity>2</capacity><comment>Very noisy</comment></room>
<room id="202"><capacity>2</capacity></room>
<room id="203"><capacity>3</capacity><comment>Very comfortable</comment></room>
</rooms>
</hotel>
</hotels>'::xml as hotels
)
-- LOCATION AS wkt
SELECT hotel_id,
hotel_name,
count(*) as total_rooms,
sum(guests_per_room) as total_guests,
ST_AsEWKT(
ST_GeogFromText(
ST_AsEWKT(
ST_SwapOrdinates(
ST_GeomFromText(CONCAT('SRID=',srid,'; ',wkt)),
'xy'
)
)
)
) as location
from (SELECT (XPATH('@id', hotel))[1]::text as hotel_id,
(XPATH('name/text()', hotel))[1]::text as hotel_name,
CAST((XPATH('location/text()',hotel))[1]::text as text) as wkt,
CAST((XPATH('location/@srid', hotel))[1]::text as text) as srid,
(XPATH('@id', rooms))[1]::text::integer as room_number,
(XPATH('capacity/text()',rooms))[1]::text::integer as guests_per_room
FROM (select hotel.*,
UNNEST(XPATH('rooms/room',hotel)) as rooms
from hoteldata as x,
unnest(XPATH('/hotels/hotel',x.hotels)) as hotel
) q
) as f
group by hotel_id, hotel_name,wkt,srid;
hotel_id hotel_name total_rooms total_guests location
valpo Valparaíso 3 7 SRID=4326;POINT(147.29756 -43.26737)
mancha La Mancha 2 8 SRID=4326;POINT(147.14756 -43.23737)

GML

The next examp[le, extracts the room information for each hotel and converts each rooms location (say a bungalow in away from the main hotel grounds). The GML is extracted and reformatted as a valid geography object.

With hoteldata as (
SELECT '<hotels>
<hotel id="mancha">
<name>La Mancha</name>
<location>
<Point srsName="EPSG:4326">
<pos>-43.23737 147.14756</pos>
</Point>
</location>
<rooms>
<room id="201">
<capacity>3</capacity>
<comment>Great view of the Channel</comment>
<location>
<Point srsName="EPSG:4326">
<pos>-43.23731 147.14751</pos>
</Point>
</location>
</room>
<room id="202">
<capacity>5</capacity>
<location>
<Point srsName="EPSG:4326">
<pos>-43.23732 147.14758</pos>
</Point>
</location>
</room>
</rooms>
</hotel>
<hotel id="valpo">
<name>Valparaíso</name>
<location>
<Point srsName="EPSG:4326">
<pos>-43.26737 147.29756</pos>
</Point>
</location>
<rooms>
<room id="201">
<capacity>2</capacity>
<comment>Very noisy</comment>
<location>
<Point srsName="EPSG:4326">
<pos>-43.26729 147.29750</pos>
</Point>
</location>
</room>
<room id="202">
<capacity>2</capacity>
<location>
<Point srsName="EPSG:4326">
<pos>-43.26741 147.29734</pos>
</Point>
</location>
</room>
<room id="203">
<capacity>4</capacity>
<comment>Very comfortable</comment>
<location>
<Point srsName="EPSG:4326">
<pos>-43.26740 147.29760</pos>
</Point>
</location>
</room>
</rooms>
</hotel>
</hotels>'::xml as hotels
)
-- Just extract Hotel entity
SELECT hotel_name,
ST_AsEWKT(
ST_GeogFromText(
ST_AsEWKT(
ST_SwapOrdinates(
ST_GeomFromGml(wkt),
'xy'
)
)
)
) as location,
room_number,
guests_per_room
from (SELECT (XPATH('name/text()', hotel))[1]::text as hotel_name,
CAST((XPATH('location/Point',
rooms,
ARRAY[ARRAY['gml','"http://www.opengis.net/gml"']]
)
)[1]::text as text)::text as wkt,
(XPATH('@id', rooms))[1]::text::integer as room_number,
(XPATH('capacity/text()',rooms))[1]::text::integer as guests_per_room
FROM (select hotel.*, rooms.*
from hoteldata as x,
unnest(XPATH('/hotels/hotel',x.hotels)) as hotel,
unnest(XPATH('rooms/room',
hotel.*,
ARRAY[ARRAY['gml','"http://www.opengis.net/gml"']]
)
) as rooms
) q
) as f
order by hotel_name, room_number;
hotel_name location room_number guests_per_room
La Mancha SRID=4326;POINT(147.14751 -43.23731) 201 3
La Mancha SRID=4326;POINT(147.14758 -43.23732) 202 5
Valparaíso SRID=4326;POINT(147.2975 -43.26729) 201 2
Valparaíso SRID=4326;POINT(147.29734 -43.26741) 202 2
Valparaíso SRID=4326;POINT(147.2976 -43.2674) 203 4

Using the COPY command.

The examples above hard-code the XML into the SQL statement.

Normally, the XML is provided as a disk file.

Here we can load the XML data using the PostgreSQL COPY command:

drop table if exists public.hoteldata;
create table public.hoteldata (
hotels text /* Loading as text allows for validation: see below */
);
COPY public.hoteldata (hotels) FROM 'C:\temp\hotels.xml';
-- Validate
select XMLPARSE (DOCUMENT a.hotels) from public.hoteldata as a;
select XMLPARSE (CONTENT a.hotels) from public.hoteldata as a;
select xml_is_well_formed(a.hotels),
xml_is_well_formed_content(a.hotels)
from public.hoteldata as a;

Once the XML is loaded, it can be queried as above:

SELECT hotel_name,
ST_AsEWKT(
ST_GeogFromText(
ST_AsEWKT(
ST_SwapOrdinates(
ST_GeomFromGml(gml),
'xy'
)
)
)
) as location,
room_number,
guests_per_room
from (SELECT (XPATH('name/text()', hotel))[1]::text as hotel_name,
CAST((XPATH('location/Point',
rooms,
ARRAY[ARRAY['gml','"http://www.opengis.net/gml"']]
)
)[1]::text as text)::text as gml,
(XPATH('@id', rooms))[1]::text::integer as room_number,
(XPATH('capacity/text()',rooms))[1]::text::integer as guests_per_room
FROM (select hotel.*, rooms.*
from public.hoteldata as x,
unnest(XPATH('/hotels/hotel',x.hotels::xml)) as hotel,
unnest(XPATH('rooms/room',
hotel.*,
ARRAY[ARRAY['gml','"http://www.opengis.net/gml"']]
)
) as rooms
) q
) as f
order by hotel_name, room_number;
hotel_name location room_number guests_per_room
La Mancha SRID=4326;POINT(147.14751 -43.23731) 201 3
La Mancha SRID=4326;POINT(147.14758 -43.23732) 202 5
Valparaíso SRID=4326;POINT(147.2975 -43.26729) 201 2
Valparaíso SRID=4326;POINT(147.29734 -43.26741) 202 2
Valparaíso SRID=4326;POINT(147.2976 -43.2674) 203 4

These points, when mapped, look like this:

I hope this article is of interest to someone out there!

STPointToCircularArc — Return a measured point by snapping provided point to the provided CircularString

Function Specification

Function [lrs].[STPointToCircularArc] (
@p_circularString geometry,
@p_vertex geometry,
@p_round_xy int = 3,
@p_round_zm int = 2
)
Returns geometry

Description

This function snaps supplied point to @p_circularString, returning the snapped point.

Computes Z and M values if exist on @p_circularString.

If input @p_circularString is 2D, length from start of @p_circularString to point is returned in M ordinate of snapped point.

Returned points ordinate values are rounded to @p_round_xy/@p_round_zm decimal digits of precision.

Notes

Supports CircularString geometries only.

If @p_point does not fall on CircularString, null is returned.

If @p_point is the same as the centre of the circle formed by the CircularString, the first point in the CircularString is returned.

Parameters

@p_circularString (geometry) - (Measured) CircularString with or without Z ordinates.
@p_point (geometry) - Point near to linestring.
@p_round_xy (int) - Decimal degrees of precision to which calculated XY ordinates are rounded.
@p_round_zm (int) - Decimal degrees of precision to which calculated ZM ordinates are rounded.

Result

This function computes position of point on CircularString. If @p_point can’t be found on CircularArc, NULL is returned.

Examples

select 'Point has relationship with XYZM circular arc' as test,
[lrs].[STPointToCircularArc] (
geometry::STGeomFromText('CIRCULARSTRING (3 6.325 -2.1 0, 0 7 -2.1 3.08, -3 6.325 -2.1 6.15)',0),
geometry::Point(2,8,0),
3,2).AsTextZM() as project_point
union all
select 'Point does not have relationship with XYM CircularSring' as test,
[lrs].[STPointToCircularArc] (
geometry::STGeomFromText('CIRCULARSTRING (3 6.325 NULL 0, 0 7 NULL 3.08, -3 6.325 NULL 6.15)',0),
geometry::Point(8,8,0),
3,2).AsTextZM() as project_point
union all
select 'Point is on centre of the circular arc' as test,
[lrs].[STPointToCircularArc] (
geometry::STGeomFromText('CIRCULARSTRING (3 6.3246 -1, 0 7 -1, -3 6.3246 -1)',0),
geometry::Point(0,0,0),
3,2).AsTextZM() as project_point
union all
select 'Point projects on to point half way along circular arc' as test,
[lrs].[STPointToCircularArc] (
geometry::STGeomFromText('CIRCULARSTRING (3 6.3246, 0 7, -3 6.3246)',0),
geometry::Point(0,3.5,0),
3,2).AsTextZM() as project_point
go
test project_point
------------------------------------------------------- -----------------------------
Point has relationship with XYZM circular arc POINT (1.698 6.791 -2.1 1.37)
Point does not have relationship with XYM CircularSring NULL
Point is on centre of the circular arc POINT (3 6.3246 -1)
Point projects on to point half way along circular arc POINT (0 7 NULL 3.1)

Processes the supplied (3D, 3DM) (multi)linestring returning the elements that intersect the specified range of elevations inclusively.

May return points and/or linestrings in the appropriate geometry type.

Where a new xy position is to be computed, the value is rounded using @p_round_xm.

Computes M values if exist on @p_linestring and rounds the values based on @p_round_zm.

Notes

Does not currently support Linestrings with CircularString elements (2012+).

Parameters

@p_linestring (geometry) - Linestring geometry with Z ordinates (could have M ordinates).
@p_start_z (float) - Start Elevation.
@p_end_z (float) - End Elevation.
@p_round_xy (int) - Decimal degrees of precision to which calculated XY ordinates are rounded.
@p_round_zm (int) - Decimal degrees of precision to which calculated ZM ordinates are rounded.

Result

This function computes and extracts the geometry of the appropriate type.

STLineSubstring — Returns a substring of the providec linestring starting and ending at the given fractions (between 0 and 1) of total 2D length or measure range.

Function Specification

Function [$(lrsowner)].[STLineSubstring] (
@p_linestring geometry,
@p_start_fraction Float,
@p_end_fraction Float = null,
@p_offset Float = 0,
@p_round_xy int = 3,
@p_round_zm int = 2
)
Returns geometry

Description

Given a start and end measure, this function extracts the line segment defined between them (a point if start=end).

If a non-zero value is supplied for @p_offset, the extracted line is then offset to the left (if @p_offset < 0) or to the right (if @p_offset > 0).

Notes

Supports linestrings with CircularString elements.

@p_linestring (geometry) - Linestring geometry with measures.
@p_start_fraction (float) - Value defining start point of located geometry.
@p_end_fraction (float) - Value defining end point of located geometry.
@p_offset (float) - Offset (distance) value left (negative) or right (positive) in SRID units.
@p_round_xy (int) - Decimal degrees of precision to which calculated XY ordinates are rounded.
@p_round_zm (int) - Decimal degrees of precision to which calculated ZM ordinates are rounded.

Notes

Current offset generation is limited; a new more robust algorithm is being developed.

Result

This function computes and returns a line between the supplied start/end measure with offset.

Example

-- Measured Linestring
Print '....Line SubString';
select [lrs].[STLineSubstring] (
geometry::STGeomFromText('LINESTRING(-4 -4 0 1, 0 0 0 5.6, 10 0 0 15.61, 10 10 0 25.4)',28355),
0.0,1.0,0.0,3,2).AsTextZM() as line
union all
select [lrs].[STLineSubstring] (
geometry::STGeomFromText('LINESTRING(-4 -4 0 1, 0 0 0 5.6, 10 0 0 15.61, 10 10 0 25.4)',28355),
0.0,0.5,0.0,3,2).AsTextZM() as line
GO
line
LINESTRING (-4 -4 0 1, 0 0 0 5.6, 10 0 0 15.61, 10 10 0 25.4)
LINESTRING (-4 -4 0 1, 0 0 0 5.6, 13.2 0 0 13.2)
-- UnMeasured LineStrings';
select [lrs].[STLineSubstring] (
geometry::STGeomFromText('LINESTRING(-4 -4, 0 0, 10 0, 10 10)',28355),
0.0,1.0,0.0,3,2).AsTextZM() as line
union all
select [lrs].[STLineSubstring] (
geometry::STGeomFromText('LINESTRING(-4 -4, 0 0, 10 0, 10 10)',28355),
0.0,0.5,0.0,3,2).AsTextZM() as line
GO
line
LINESTRING (-4 -4, 0 0, 10 0, 10 10)
LINESTRING (-4 -4, 0 0, 7.172 0)

STLineInterpolatePoint — Returns point geometry at supplied fraction along linestring.

Function Specification

Function [$(lrsowner)].[STLineInterpolatePoint] (
@p_linestring geometry,
@p_fraction Float
@p_round_xy int = 3,
@p_round_zm int = 2
)
Returns geometry

Description

Given a fraction between 0 and 1.0, this function returns a geometry point at the position described by that ratio.

Ratio is combined with length, so @p_ratio of 1.0 is equivalent to @p_linestring.STLength() ie @p_linestring.STEndPoint().
For example, @p_ratio value of 0.5 returns point at exact midpoint of linestring (ct centroid).

Supports measured and unmeasured linestrings.

Supports LineStrings with CircularString elements.

@p_linestring (geometry) - Linestring (including CircularString) geometry.
@p_ratio (float) - Length ratio between 0.0 and 1.0. If Null, @p_linestring is returned.
@p_round_xy (int) - Decimal degrees of precision to which calculated XY ordinates are rounded.
@p_round_zm (int) - Decimal degrees of precision to which calculated ZM ordinates are rounded.

Result

The function calculates and returns point at provided measure/length fraction from start.

Examples

-- Linestring
select f.fraction,
[$(lrsowner)].[STLineInterpolatePoint] (
@p_linestring geometry::STGeomFromText('LINESTRING(-4 -4 0 1, 0 0 0 5.6, 10 0 0 15.61, 10 10 0 25.4)',0),
@p_fraction f.fraction,
@p_round_xy 4,
@p_round_zm 3
).AsTextZM() as fPoint
from (select 0.01 * CAST(t.IntValue as numeric) as fraction
from [dbo].[Generate_Series](1,100,10) as t
) as f
order by f.fraction
GO
fraction fPoint
0.01 POINT (-3.8186 -3.8186 0 1.046)
0.11 POINT (-2.0044 -2.0044 0 1.506)
0.21 POINT (-0.1902 -0.1902 0 1.966)
0.31 POINT (2.2968 0 0 6.496)
0.41 POINT (4.8625 0 0 7.497)
0.51 POINT (7.4281 0 0 8.498)
0.61 POINT (9.9938 0 0 9.499)
0.71 POINT (10 2.5595 0 16.587)
0.81 POINT (10 5.1252 0 17.566)
0.91 POINT (10 7.6909 0 18.545)
-- Unmeasured 2D Compound curve test.
select f.fraction,
[$(lrsowner)].[STLineInterpolatePoint] (
geometry::STGeomFromText('COMPOUNDCURVE(CIRCULARSTRING (3 6.3246, 0 7, -3 6.3246),(-3 6.3246, 0 0, 3 6.3246))',0),
f.fraction,
4,
3
).AsTextZM() as fPoint
from (select 0.01 * CAST(t.IntValue as numeric) as fraction
from [dbo].[Generate_Series](1,100,10) as t
) as f
order by f.fraction
GO
fraction fPoint
0.01 POINT (2.8163 6.4085)
0.11 POINT (0.876 6.945)
0.21 POINT (-1.1367 6.9071)
0.31 POINT (-2.9736 6.269)
0.41 POINT (-2.1079 4.4439)
0.51 POINT (-1.2421 2.6187)
0.61 POINT (-0.3764 0.7935)
0.71 POINT (0.4893 1.0316)
0.81 POINT (1.3551 2.8568)
0.91 POINT (2.2208 4.682)

STLineLocatePoint — Returns a float between 0 and 1 representing the location of the closest point on LineString to the given Point

Function Specification.

Function [$(lrsowner)].[STLineLocatePoint] (
@p_linestring geometry,
@p_point geometry,
@p_round_xy int = 3,
@p_round_zm int = 2
)
Returns geometry

Description.

This function, given a point near a the supplied measure @p_linestring, returns the measure/length ratio of the found position.

Notes.

Is identical to PostGIS’s ST_LineLocatePoint.

Srid of @p_linestring and @p_point must be the same.

If @p_linestring is measured ratio returned is measure of located point / MeasureRange of linestring.

If @p_linestring is not measured the ratio returned is position of located point from start / STLenth of linestring.

Parameters.

@p_linestring (geometry) - Linestring geometry with or without measures.
@p_point (geometry) - Point near to linestring.
@p_round_xy (int) - Decimal degrees of precision to which calculated XY ordinates are rounded.
@p_round_zm (int) - Decimal degrees of precision to which calculated ZM ordinates are rounded.

Result.

The function returns the ratio of the position of the point on @p_linestring using measure/measure range or length/total length.

Example.

select [lrs].[STLineLocatePoint] (
geometry::STGeomFromText('LINESTRING(-4 -4 0 1, 0 0 0 5.6, 10 0 0 15.61, 10 10 0 25.4)',28355),
geometry::Point(8,8,28355),
default,
default
) as ratio
union all
select [lrs].[STLineLocatePoint] (
geometry::STGeomFromText('LINESTRING(-4 -4,0 0,10 0,10 10)',28355),
geometry::Point(10,0,28355),
4,
8
) as ratio
GO
ratio
23.44
15.6

STIsPseudoMultiCurve — Checks if supplied @p_geometry is a GeometryCollection object containing only LineString, CircularString and CompoundCurve elements.

Function Specification.

Function [dbo].[STIsPseudoMultiCurve] (
@p_geometry geometry
)
Returns bit

Description.

This function checks if the supplied @p_geometry is a GeometryCollection object containing only LineString, CircularString and CompoundCurve elements.

One cannot create a MultiLineString object where an element is a CircularString or CompoundCurve.

Any attempt to do so causes the GeometryType to be set to GeometryCollection rather than MultiLineString or MultiCurve.

If these sorts of GeometryCollection objects are not supported, it is impossible to represent a MultiLine object with a mix of CircularCurve and LineString types.

The function returns 1 (true) if provided geometry is a pseudo MultiCurve (or MultiLineString).

Parameters.

@p_geometry (geometry) - GeometryCollection or MultiLineString.

Result.

This function returns 1 (true) if @_geometry is a pseudo MultiCurve, 0 (false) otherwise.

Example.

-- This is how SQL Server merges a LineString and a CircularString that do not touch
--
SELECT geometry::STGeomFromText('LINESTRING(0 0 0 5.6, 5 5 0 6.3,5 10 0 9.6)',0)
.STUnion(
geometry::STGeomFromText('CIRCULARSTRING(-0.5 -0.5 0 5.6,-0.2 -0.4 0 5.5, -0.1 -0.1 0 5.65)',0)
).AsTextZM() as MultiCurve;
GO
MultiCurve
--------------------------------------------------------------------------------------------------
GEOMETRYCOLLECTION (LINESTRING (5 10, 5 5, 0 0), CIRCULARSTRING (-0.5 -0.5, -0.2 -0.4, -0.1 -0.1))
-- Note that STUnion removes Z and M ordinates
-- Now put result through STIsPseudoMultiCurve
--
SELECT [dbo].STIsPseudoMultiCurve] ( geometry::STGeomFromText('GEOMETRYCOLLECTION (LINESTRING (5 10, 5 5, 0 0), CIRCULARSTRING (-0.5 -0.5, -0.2 -0.4, -0.1 -0.1))',0)) as isMultiCurve
GO
isMultiCurve
------------
1