If I run the following query in PostgreSQL 9.2 I get the geometry:
select st_geomfromtext('POLYGON((-123.13974380493164 49.258142464379425,-123.11695575714111 49.25450118750378,-123.11914443969727 49.24965507167121,-123.13845634460449 49.25158797259088,-123.14326286315918 49.254669252349466,-123.14257621765137 49.25595773048919,-123.13974380493164 49.258142464379425))')
from park
However, if I try to put the st_geomfromtext() into an st_within, like this:
select objectid
from park
where st_within(shape,st_geomfromtext('POLYGON((-123.13974380493164 49.258142464379425,-123.11695575714111 49.25450118750378,-123.11914443969727 49.24965507167121,-123.13845634460449 49.25158797259088,-123.14326286315918 49.254669252349466,-123.14257621765137 49.25595773048919,-123.13974380493164 49.258142464379425))'),4326) = 't'
I get:
function st_within(sde.st_geometry, geometry, integer) does not exist…You might need to add explicit type casts
Looks like I need to cast my WKT as st_geometry somehow. Any suggestions?
Best Answer
You are mixing up PostGIS spatial columns and sde spatial columns. Your 'shape' column is using the Esri spatial type. Your ST_GeomFromText() function is creating a PostGIS spatial type. And the ST_Within() function is complaining that you're mixing types. Figure out what the Esri equivalent to ST_GeomFromText() is, and use that, so your types match.