1

I am trying to create a spatial view in ArcMap where I run a ST_Intersect against a ST_Geometry point dataset and a PostgreSQL geometry polygon dataset. Both datasets are the same SRID. I am having an issue converting the PostgreSQL geometry to ST_Geometry. I haven't ever done anything like this before. Where I am going wrong?

Below is the query I have been working with. I am receiving an error on line 6 stating "function sde.st_geometry(geometry, integer) is not unique at [b.shape]"

SELECT a.objectid,
    a.shape,
    b.objectid AS oid
FROM cd.points a,
    sw.risk_polygons b
WHERE sde.st_intersects(a.shape, sde.st_geometry(b.shape, 4326)) = 1
ORDER BY a.objectid;
1
  • 1
    You can't just cast between the formats. You need to use Well-Known Text or Well-Known Binary to translate between them. You should also use a JOIN instead of an old-style mutli-FROM list. – Vince Jan 6 '20 at 19:19
3

While PostGIS uses Well-Known Binary internally, you'd be better off explicitly casting the PostGIS geometry to Well-Known Text or Binary.

Try

SELECT a.objectid,
       a.shape,
       b.objectid AS oid
FROM   cd.points a
JOIN   sw.risk_polygons b ON sde.st_intersects(a.shape, sde.st_geometry(ST_AsText(b.shape), 4326)) = 1
ORDER  BY a.objectid;

or

SELECT a.objectid,
       a.shape,
       b.objectid AS oid
FROM   cd.points a
JOIN   sw.risk_polygons b ON sde.st_intersects(a.shape, sde.st_geometry(ST_AsBinary(b.shape), 4326)) = 1
ORDER  BY a.objectid;

The query would be faster if you chose one geometry type and used that across the board. But you should be able to build a covering index by creating a custom function to perform both the cast and well-known export in one go, so you don't need to pay the conversion cost with each query (provided the polygons are relatively static).

3
  • How would this work if the data was versioned? – DPSSpatial Jan 6 '20 at 20:43
  • Exactly the same as all versioned view queries on all versioned tables, or all non-versioned queries on versioned tables, as you well know. The real issue could be if CAD data was associated with the polygons, since then the linework in the geometry might not correspond to the CAD BLOB. – Vince Jan 6 '20 at 20:53
  • Thanks for your help. I tested both of these methods and progressed beyond my initial issue. At first, I was getting shape errors for both, but for unknown reasons, the ST_AsText method has started working. (The ST_AsBinary error is "error converting shape from ESRI shape") – MCline Jan 7 '20 at 16:30

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.