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

  • 1
    Please provide example texts for all three kinds of geometries. – underdark Nov 30 '12 at 6:59
  • Added the sample text – Yun Dec 2 '12 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 '12 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. – Nicklas Avén Dec 3 '12 at 8:28

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

| improve this 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;
(1 row)


| improve this answer | |
  • 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 '12 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 '12 at 7:55

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

| improve this answer | |
  • 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 '12 at 3:15
  • 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. – Paul Ramsey Dec 3 '12 at 19:49

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

| improve this answer | |

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.