[GIS] the easiest way to detect geometry type from text in PostGIS

geometrypostgispostgresql

I have some records with different types of 'geometries (poly,line,points)' stored as string. I'm trying to use ST_GeomFromText as I want to do some intersection with them. What is the easiest way to detect whether I should put ST_GeomFromText(Polygon or LineString or MultiPolygon or Point in the SQL besides splitting the string and counting the coords pair?

Example data:

71.1776585052917 42.3902909739571 

-71.1776585052917 42.3902909739571,-71.1776820268866 42.3903701743239,
-71.1776063012595 42.3903825660754,-71.1775826583081 42.3903033653531,-71.1776585052917 42.3902909739571

-71.1776585052917 42.3902909739571,-71.1776820268866 42.3903701743239,
-71.1776063012595 42.3903825660754,-71.1775826583081 42.3903033653531

So the first one should be point, the second a polygon, the third is a linestring.
But programmatically i don't know how to differentiate them, unless I split the string up count them up and if it's 1 pair it's point, etc…

Best Answer

I think ST_GeometryType is what you're looking for. Example of what you get from querying a point geometry:

SELECT ST_GeometryType(geom) FROM table WHERE column=value;
 st_geometrytype 
-----------------
 ST_Point
(1 row)

https://postgis.net/docs/ST_GeometryType.html

Related Solutions

[GIS] PostGIS – Intersects feature to split into linestring

First, for polygons, you want the outer boundary of the polygon as a line using ST_Boundary. Then you want a combination of ST_Intersection and ST_SymDifference

So something like (untested):

WITH firststep AS( SELECT id, ST_BOUNDARY(geom) AS boundary
FROM polygons)

SELECT p.id, ST_Intersection(boundary, geom), o.id
FROM firststep p
INNER JOIN othergeoms o ON ST_INTERSECTS(boundary, geom)

UNION

SELECT p.id, ST_SymDifference(boundary, geom), o.id
FROM firststep p
INNER JOIN othergeoms o ON ST_INTERSECTS(boundary, geom)

[GIS] Polygon intersection with LineString in PostGis

If I understand your question correctly, I think @gcarillo has the right idea in his comment. Rather than building the linestrings before the intersection, determine the points inside the polygon first and then build the linestrings based on sequence groups.

Although it looks a little complex, the following query will achieve this. I've made some assumptions on the likely structure of your gps points table.

-- Build complete lines within the polygon
SELECT startTime, 
       endTime, 
       ST_MakeLine(geom_array) AS line
FROM (
    -- Aggregate the points based on sequence group
    SELECT min(captureTime) AS startTime, 
           max(CaptureTime) AS endTime, 
           array_agg(location) AS geom_array, 
           grp
    FROM (
        -- Intersect the points with a polygon and determine a group for the sequence
        SELECT *, 
               seq - ROW_NUMBER() OVER (ORDER BY captureTime) AS grp
        FROM (
            -- Sequence the points based on time captured
            SELECT captureTime, 
                   location, 
                   ROW_NUMBER() OVER (ORDER BY captureTime) AS seq 
            FROM gpsPoints
            ) orderPoints
        WHERE ST_Intersects(location,ST_MakeEnvelope(1,1,4,4)) --Replace envelope with polygon
        ) pointsInside
    GROUP BY grp
    ) pointGroup;

This will pick up points on the boundary as well as inside. Single unconnected points will be turned into a single point linestring and should be handled or removed from the query results.

Edit: Changed the sort column for the grp row_number() to get a better execution plan (one less sort).

Example data:

Best Answer

Related Solutions

[GIS] PostGIS – Intersects feature to split into linestring

[GIS] Polygon intersection with LineString in PostGis

Related Question