4

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

4
  • 1
    Please provide example texts for all three kinds of geometries.
    – underdark
    Nov 30, 2012 at 6:59
  • Added the sample text
    – Yun
    Dec 2, 2012 at 23:08
  • At least all your polygons are closed, right? The first point has to be always equal to the last one. Otherwise there is no chance ...
    – underdark
    Dec 3, 2012 at 6:54
  • 1
    The data will never tell you if it is a polygon or a closed linestring. It is up to you to know or judge. Dec 3, 2012 at 8:28

4 Answers 4

8

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

2
  • 1
    Hi Thx. But my input is a string, so the table column is a varchar / text data type. The ST_GeometryType needs the geom, while I'm trying to create the geom from the string. I'm trying to find out what text to put in in the ST_GEOMFROMTEXT function, e.g. "POINT"
    – Yun
    Nov 30, 2012 at 6:14
  • In that case, my answer won't be useful, sorry. It would be helpful if you gave some sample data - are your strings just lists of coordinate pairs?
    – L_Holcombe
    Nov 30, 2012 at 7:55
3

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

0

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

0

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

2
  • Ok if there's no other alternative, I just wonder if perhaps there's a function to do that or something more efficient.
    – Yun
    Dec 3, 2012 at 3:15
  • 1
    No, the parser keys on the type name. There's too many things different nestings of coordinates could mean: is it a Polygon or a MultiLineString? Is it a MultiLineString or a CompoundCurve? Is it a CircularString or a LineString? You just can't know from the coordinate arrays alone. At your application level you probably can figure it out contextually, knowing something about your problem domain, but I can't figure it out in generality. Dec 3, 2012 at 19:49

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.