0

I'm using SQL Server and WKT to deal with geographic data. What I'd like to do is have something like :

SELECT pt FROM point_table, polygon_table where pt.point_table WITHIN ply.polygon_table

Here pt is WKT point. Ex : 'POINT(lon lat)'

and ply is WKT polygon Ex : 'POLYGON (( lon1 lat1 , lon2 lat2 , lon3 lat3 , lon4 lat4 ))'

  • You have the order backwards in all your vertices. If you think "lon,lat", you'll be sure to code it correctly. – Vince Jun 22 '16 at 11:17
  • The format for Well-Known Text is well documented. It does not have the extra parenthesis around vertices, and POLYGON always starts/ends with a double-paren. The Microsoft WITHIN operator is documented. You have three unrelated tags. Please edit the question. – Vince Jun 22 '16 at 11:23
  • @Vince Post edited. Thank you mate. Microsoft WITHIN for geographic object is hat I was looking for. You could add this as an answer if you want. – Kurt Miller Jun 22 '16 at 11:34
0

Microsoft's implementation of the spatial types and functions standard is a bit quirky --

  • The ST functions don't have an underscore between the ST and the function name (e.g. STWithin vice ST_Within)
  • Rather than taking two operands, the operators are a function of the Geometry/Geography datatype (e.g., pnt.geom.STWithin(ply.geom) vice ST_Within(pnt.geom,ply.geom))
  • The function names are case sensitive (STWithin works, stwithin doesn't)

Assuming you have the data in Geometry format in columns with name "geom" and id columns "idcol", then the point within polygon join could look like:

SELECT ply.idcol plyid, pnt.idcol pntid
FROM polygon_table ply
INNER JOIN point_table pnt
    ON pnt.geom.STWithin(ply.geom) = 1
0

Here's an example of intersecting a geometry point with a WKT polygon:

declare @tempPoly geometry;

set @tempPoly = geometry::STGeomFromText('POLYGON ((3168400.501482 1706746.617880,3131160.918149 1690210.159546,3164754.668149 1673022.659546,3174650.501482 1688517.451213,3173869.251482 1700366.409546,3168400.501482 1706746.617880))', 2877);

select * from pointstable as pt

--this is where you link the temporary geometry to your spatial table

 where pt.shape.STIntersects(@tempPoly) = 1

So basically the opposite would have a where clause similar to:

 where @tempPoint.STIntersects(polygonTable.shape) = 1

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.