I am having an issue using PostGIS (1.5.4) data. It may be that I'm just not familiar enough with this technology to see the obvious (I'm a regular expert with nearly 4 hours of experience), but I am running into an error that I have been unable to solve with Google.
I have a table which includes Polygon data (and yes, I checked; the column type is geometry, not polygon- the Postgres native type). The problem arises when I am trying to run a query on the table to find which shape contains a particular problem.
I am using the following query:
WHERE ST_Contains(geoshp_polygon, POINT(-97.4388046000, 38.1112251000));
The error I receive is 'ERROR: function st_contains(geometry, point) does not exist'. I tried using a CAST() function, but got 'ERROR: cannot cast type geometry to polygon'. I'm guessing the issue has to do with the way the data is stored- PGAdmin shows it as hex data. I tried using
ST_GeomFromHEXEWKB() just on a hunch, but received 'ERROR: function st_geomfromhexewkb(geometry) does not exist'.
I'm fairly confused as to what the issue is here, so any ideas at all would be much appreciated.
st_contains needs a geom,geom as arguments...
Give this a try...
SELECT * FROM geo_shape WHERE ST_Contains(geoshp_polygon, GeomFromText('POINT(-97.4388046000 38.1112251000)'));
Editted to correct , issue in the point data. ST_geomfromtext will work, kinda curious what the difference is there
You cannot mix PostgreSQL's geometric data types with PostGIS's geometry type, which is why you see that error. I suggest using one of PostGIS's geometry contstructors to help out:
SELECT * FROM geo_shape WHERE ST_Contains(geoshp_polygon, ST_SetSRID(ST_MakePoint(-97.4388046000, 38.1112251000),4326);
Or a really quick text way is to piece together the well-known text:
SELECT 'SRID=4326;POINT(-97.4388046000 38.1112251000)'::geometry AS geom;
(this will output the WKB for the geometry type).