CODEWITHSUNDEEP
postgresqlpostgis
Crash
Crash

Reputation: 219

PostGIS Conversion Issues

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:

SELECT * 
FROM geo_shape 
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.

Upvotes: 1

Views: 4932

Answers (2)

Twelfth
Twelfth

Reputation: 7180

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

Upvotes: 2

Mike T
Mike T

Reputation: 43622

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

Upvotes: 2

Related Questions