topic Re: MSSQL server and Spatial query on ArcSDE Database in Data Management Questions

MSSQL server and Spatial query on ArcSDE Database

FrancisMoisan — Fri, 26 Sep 2014 14:05:35 GMT

Hi!

We store our spatial data into a SDE geodatabase (with sde geometry) into a MSSQL server 2008 database.

I need to create a SQL query with spatial relation but need to perform the query onto the database wichi I think will be faster than a tool from ArcGIS.

I can do it with model builder or Python but very slow and need to write a table down into the database.

I just need to analyse and return a count of the result...

When I was using Oracle my query was :

SELECT *

FROM [my_points] pts, [my_area] poly

WHERE CODE_MUN NOT LIKE 'NR%'

and sdo_inside(pts.geoloc,poly.geom) = 'TRUE'

and pts.CODE_MUN <> poly.MUS_CO_GEO

How to do this into MSSQL server with sdebinary.

Any idea?

thanks!

Re: MSSQL server and Spatial query on ArcSDE Database

JoshuaBixby — Fri, 26 Sep 2014 15:22:55 GMT

SDEBINARY? I don't think it is possible, regardless of Oracle or SQL Server. Are you using SDEBINARY with the code snippet above? It looks like SDO_Geometry. With SQL Server, you would need to use GEOMETRY or GEOGRAPHY.

Look at STWithin or STContains depending whether you want to look at it from the point's or polygon's perspective.

Re: MSSQL server and Spatial query on ArcSDE Database

FrancisMoisan — Fri, 26 Sep 2014 19:08:45 GMT

Thanks,

No the code snippet is the Oracle version of my query. Oracle data where store in native Oracle.

It was so easy...

SDE means using arcgis and I don't like it...

I must validate millions of data with spatial join and performance is not good.

I was using FME to perform sql query (both spatial or not) in Oracle and it was very fast...

I want to reproduce this in MSSQL server with ArcSDE data but can't find a way...

Re: MSSQL server and Spatial query on ArcSDE Database

JoshuaBixby — Fri, 26 Sep 2014 20:02:59 GMT

Are you not able to use SQL Server's native spatial types (GEOMETRY, GEOGRAPHY)? Either native spatial type works with ArcSDE and allows one to perform SQL directly against the data. If the data is registered as version, you may need to query the versioned view instead of the base tables.

Re: MSSQL server and Spatial query on ArcSDE Database

VinceAngelo — Fri, 26 Sep 2014 21:23:48 GMT

GEOMETRY has been the default geometry storage since ArcGIS 10.1, I believe (certainly since 10.2), but that only applies to new installations. SQL-Server 2008R2 is the least recent supported Microsoft database, so you may be using both older ArcGIS and RDBMS.

In the future, I recommend you post geedatabase management questions in GIS > Managing Data, and use a "geodatabase" tag, rather than relying on the "every post by time" feed for geodatabase experts to find your post.

- V