I was reading a postgis tutorial and noticed that unlike working with shapefiles, one can actually store different types of geometry in postgres. In a table created with multiple geometry types, the tutorial used:

SELECT ST_X(geom), ST_Y(geom)
  FROM geometries
  WHERE name = 'Point';

to show X, Y coordinates of a point. I was wondering in general, whether we can use the geometry type in the WHERE clause to do the same. I tried:

SELECT ST_X(geom), ST_Y(geom), name
  FROM geometries
  WHERE ST_geometrytype(geom) = ST_Point;

and got:

ERROR:  column "st_point" does not exist
LINE 3:   WHERE ST_geometrytype(geom) = ST_Point;
********** Error **********

ERROR: column "st_point" does not exist
SQL state: 42703
Character: 87

How can I fix this?

  • 1
    The operator is defined as returning type text. You'll need to quote your equivalence value WHERE ST_geometrytype(geom) = 'ST_Point' – Vince Oct 14 '14 at 17:48

Your Line ...

WHERE ST_geometrytype(geom) = ST_Point;

Indicates that ST_Point is a column since it is not in quotes. I see here also a comment from @Vince that is saying basically the same thing that I am. Since ST_Point is not quoted in your expression is is trying to evaluate what is in ST_geometrytype(geom) against another field/column and it can't find a column by that name. Just change the line I quoted above to ...

WHERE ST_geometrytype(geom) = 'ST_Point';

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.