Posted on

STMulti

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

I hope this function is of use to someone.

Posted on

STRound

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)

I hope this function is of use to someone.

Posted on

Using PostgreSQL’s XML processing to load spatial data

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:

<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>
  </rooms>
 </hotel>
</hotels>

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!

Posted on

STPointToCircularArc

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)
Posted on

STLocateBetweenElevations

STLocateBetweenElevations — Computes and returns elements that intersect the specified Z range.

Function Specification

Function [$(lrsowner)].[STLocateBetweenElevations] (
               @p_linestring geometry,
               @p_start_z    Float,
               @p_end_z      Float = null,
               @p_round_xy   int   = 3,
               @p_round_zm   int   = 2
             )
     Returns geometry 

Description

Is implementation of PostGIS:

      geometry ST_LocateBetweenElevations(geometry geom_mline, 
                                          float8 elevation_start,
                                          float8 elevation_end);

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.

Examples

-- PostGIS 1
select [$(lrsowner)].[STLocateBetweenElevations](
         geometry::STGeomFromText('LINESTRING(1 2 3, 4 5 6)',0),
         2,4, 
         3,2
       ).AsTextZM() as geomZ;

geomz
LINESTRING (1 2 3, 2 3 4)

-- PostGIS 2
select [$(lrsowner)].[STLocateBetweenElevations](
         geometry::STGeomFromText('LINESTRING(1 2 6, 4 5 -1, 7 8 9)',0),
         6,9, 
         3,2
       ).AsTextZM() as geomZ;
 
geomz
GEOMETRYCOLLECTION (POINT (1 2 6), LINESTRING (6.1 7.1 6, 7 8 9))

-- PostGIS 3
SELECT d.geom.AsTextZM() as geomWKT
  FROM (SELECT [$(lrsowner)].[STLocateBetweenElevations](
                 geometry::STGeomFromText('LINESTRING(1 2 6, 4 5 -1, 7 8 9)',0),
                 6,9,
                 3,2
               ) As the_geom
       ) As foo
       cross apply
       [$(owner)].[STExtract](foo.the_geom,default) as d;

geomWKT
POINT (1 2 6)
LINESTRING (6.1 7.1 6, 7 8 9)
Posted on

STLineSubstring

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.

Supports measured and unmeasured linestrings.

Is wrapper over STFindSegmentByMeasureRange.

Provides implementation of PostGIS’s ST_LocateBetween(geometry geomA, float8 measure_start, float8 measure_end, float8 offset);

Inputs

    @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)
Posted on

STLineInterpolatePoint

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.

Notes

Wrapper over lrs.STFindPointByRatio

Implements PostGIS ST_LineInterpolatePoint function.

Parameters

    @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)
Posted on

STLineLocatePoint

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
Posted on

Wrapper Functions for PostGIS geography

Having done a lot of work with PostGIS geometry and geography I decided that I needed some wrapper functions to force more consistency in my use.

The wrapper functions are as follows are are available at GitHub

ST_AddMeasure(geography,float8,float8);
ST_AddPoint(geography,geography);
ST_AddPoint(geography,geography,integer);
ST_CoordDim(geography,geography);
ST_Dimension(geography);
ST_EndPoint(geography);
ST_ExteriorRing(geography);
ST_FlipCoordinates(geography);
ST_GeometryN(geography,integer);
ST_GeometryType(geography);
ST_InteriorRingN(geography,integer);
ST_IsClosed(geography);
ST_IsEmpty(geography);
ST_IsValid(geography);
ST_IsValid(geography,integer);
ST_IsValidDetail(geography);
ST_IsValidDetail(geography,integer);
ST_IsValidReason(geography);
ST_IsValidReason(geography,integer);
ST_M(geography);
ST_NPoints(geography);
ST_NumGeometries(geography);
ST_NumInteriorRings(geography);
ST_NumPoints(geography);
ST_PointN(geography,integer);
ST_Points(geography);
ST_RemovePoint(geography,integer);
ST_SetPoint(geography,integer,geography);
ST_StartPoint(geography);
ST_X(geography);
ST_Y(geography);
ST_Z(geography);

Posted on

STIsPseudoMultiCurve

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