[GIS] Selecting elements of specific geometry type in PostGIS

geometrypostgissql

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?

Best Answer

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';
Related Question