What is the easiest way to detect geometry type from text in PostGIS?

Question

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...

Answer 1

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

Answer 2

From example text:

Split string with , Then if returned array length is 1 then it's point , if more then its line if last coordinate pair isn't same as first.

OGC Polygon has same point at it first and last member.

After that you need to check linestrings and polygon with IsValid() function

Answer 3

you can try this:

SELECT ST_GeometryType(ST_SetSRID(ST_MakePoint(latitude, longitude), 4326))
        FROM myTable;

latitude and longitude are your x,y column.

i hope it helps you..

Answer 4

Just split the text and count the coordinates, there's no reason not to.