Subscribe to this thread
Home - General / All posts - Projection support in SQL Server
artlembo


3,400 post(s)
online
#06-Jun-17 15:06

While this looks like a question more suitable for a SQL Server blog, I think it is worth posting here to get thoughts. I've been recreating all of my SQL queries from my Manifold SQL, Postgres SQL, and SpatiaLite SQL workshops into SQL Server. So far, so good - until I get to having to reproject data (i.e. take lat/lon and reproject to State Plane or something like that).

I have done a bunch of searching, and as of 2017 in the year of our Lord, Microsoft does not support geometric transformation! The official blog moderator from Microsoft in 2016 said that you should reproject data outside of SQL Server using QGIS, ArcGIS, or OGR2OGR and then bring the data in. Therefore, if you have data in lat/lon, you can't get distances in meters.

Now, some hard working souls have written their own transformation in SQL with tons of cosines, sines, tangets, arctans, etc., but seriously, that is insane.

I'm stunned to see this. I mean they have STIntersect, STBuffer, STWhatever_you_want_do_to, but no ST_Transform? For cryin' out loud, ST_Tranform is PostGIS is open source - the code already exists!

But, here is why I wanted to post it here, I've been wondering what the reason for this is:

1. Does Microsoft not see the needs for coordinate projection?

2. Are there really not many people using SQL Server to manage spatial data, so there's nobody to complain about it?

3. Are the people using SQL Server to manage spatial data unaware of principles of coordinate system?

4. Something else?

Maybe I'm missing something here....

Finally, simply use Radian or 8 to reproject the data in SQLServer I suppose.

hugh
200 post(s)
#06-Jun-17 16:22

maybe competition from this?

revolutionanalytics.com

mdsumner


4,260 post(s)
#06-Jun-17 16:28

Wowsers that is interesting.

(hugh: AFAIK all variants of R rely on the standard stuff PROJ.4 and GDAL. R Server is mostly a thin wrapper around a redistribution of GNU R, with a thick legal shell)


https://github.com/mdsumner

adamw


10,447 post(s)
#07-Jun-17 07:14

I think they stay away from projecting coordinates because they don't want to get involved with coordinate system definitions. What should they use? EPSG? First, that's already a lot of work and a lot of internal baggage specific to EPSG (deprecated systems, broken references, etc). Second, once EPSG is in, you aren't done yet, there are plenty of coordinate system types that aren't in EPSG, same for datums, same for transforms. Also, there are custom coordinate systems (ie, Lambert Conformal Conic with parameters A, B, C on WGS72 with no record for this specific combination found in EPSG or anywhere really, it's a custom coordinate system). You need to figure out a way to define all this. And this has to co-exist with EPSG. And EPSG is not a fixed thing either, it is adding (and changing / removing) things over time. It's not a small thing and, most importantly, there is no solution that would be considered standard. I think SQL Server folks just feel that this isn't their fight and so they allow users to put whatever semantics they want into projection codes and concentrate on geometry / indexing.

artlembo


3,400 post(s)
online
#07-Jun-17 14:56

I get that, but it's not like nobody else is doing it. Also, The first use scenario that comes to mind is that I work for an insurance company and I have all of my clients addresses in lat long. I then want to test to see if they really are traveling the distance to work that they say they are. SQL server does not make this easy – yet, it would seem to be such a fundamental capability.

Similarly, a bank wants to know how many of their customers are within 5 miles of an ATM.

To me, these examples just seem so fundamental. I can't imagine how your average person would go ahead and solve it with SQLServer

jockeryl
178 post(s)
#08-Jun-17 04:55

These two examples works well in SQL Server as long as the data sources are compatible, however automatic conversion between local SRID and Lat Lon WGS84 is not included in the standards.

Planning data types and SRID when importing the data is important, luckily we can use Manifold/Radian for such a ETL/Conversion process, from one DB/Table to another.

jockeryl
178 post(s)
#07-Jun-17 08:22

Read up on the difference between Geometry and Geography data type in SQL Server, actually it's OGC / ISO standard.

https://docs.microsoft.com/en-us/sql/relational-databases/spatial/spatial-data-types-overview

The recommendations is to use SQL to store Lat/Lon data in Geography data type and store X/Y projections in Geometry datatype.

Processing of data and conversion in between systems is better done with Manifold, ESRI, FME, QGIS, OGR etc.

As Adam point out, Microsoft most likely want to avoid having to update SQL Server each time EPSG changes, they focus on querying, storing and indexing your data.

Most enterprises don't want to update a SQL Server every few months, they even wait 7-10 years and only change when their other IT systems change or go out of support.

jockeryl
178 post(s)
#08-Jun-17 04:50

I guess it has to do with their support/maintenance guarantees, release cycles and standards compliance. If this page is correct, then Microsoft is following the standards that are specified by OGC and ISO:

https://postgis.net/docs/using_postgis_dbmanagement.html

However, PostGIS as well as Oracle are adding their own extensions which has yet to be included in the above standards, including 3D and SRID support.

If you build a solution to work only for one type of SQL accent, there will always be issue to move to another DBMS, this has been true for most of my career, moving systems from Oracle to SQL Server, always small differences in SQL and stored procedures. Ex: SDO_GEOM.SDO_DISTANCE (Oracle) <-> geography.STDistance (SQL Server)

PostGIS and Oracle are going in different directions with their extensions, and a lock-in is guaranteed if you choose either solution.

Microsoft is not extending the Spatial features much for the 2016/2017 releases:

https://blogs.msdn.microsoft.com/psssql/2016/03/03/sql-2016-it-just-runs-faster-native-spatial-implementations/

I agree that Microsoft could be pushing more extension features into their product at a faster pace, but only as per industry standards or as Open Source extensions.

If they would add more and more proprietary custom extensions then they risk falling into same trap as in the early days of IE/Netscape, where the two browsers didn't follow same HTML standards and deviated from each other, which still today are causing many enterprises headache as the standards have evolved since they built their huge enterprise app. When a company invest, they expects the standards to be there 10 years later and their system to run without a need to upgrade.

Microsoft support their SQL Server products for minimum 10 years, PostgreSQL support their product 5 years.

PostGIS is not covered by any minimum and it relies on 3rd party companies to support from the Open Source community.

It's my experience that most enterprises buying these kind of systems have to consider long term support in the bid.

For most of my customers the Spatial storage is just a small part of the reason why they use SQL Server. They like the integration of Spatial with ETL, BI, R Analytics, Reporting, Mobile and Cluster/HA which are all features that are included in SQL Server.

It's easier for us to integrate and support one vendor solution than a mix of technologies and vendors, which is very important.

SQL Server certainly have limitations as you have noticed, SRID is a bit more headache as we have to process outside with FME, OGR, Manifold or ESRI.

New spatial data sources like 3D CAD, Lidar and BIM is coming more and more and I don't expect that SQL Server will be able to keep up in a reliable way, flexible integration with other systems will be more important.

Manifold and now Radian are great tools to avoid this headache and have a powerful ETL/GIS service layer in between the databases and the customer applications, they are certainly more economical choices than ESRI and FME for the same job.

Dimitri


7,413 post(s)
#08-Jun-17 17:00

Manifold and now Radian are great tools to avoid this headache and have a powerful ETL/GIS service layer in between the databases and the customer applications, they are certainly more economical choices than ESRI and FME for the same job.

Tell your friends! :-)

Also... anything you need to make Radian better for ETL or more effective for your needs in any other way working with SQL Server or any other DBMS, let us know. Even if it is just some small details, don't hesitate.

artlembo


3,400 post(s)
online
#08-Jun-17 17:47

I agree that Radian is perfect for this aspect. And, what I've read here confirms that SQLServer really isn't operating in this territory.

Another good reason for "one ring to rule them all".

Still, I scratch my head that they haven't put in an ST_Transform, or even a ST_Distance with a boolean value to return spheroidal distances.

Manifold User Community Use Agreement Copyright (C) 2007-2021 Manifold Software Limited. All rights reserved.