This project is read-only.

SRIDs in MsSqlSpatial

Topics: Developer Forum, User Forum
Feb 6, 2007 at 9:15 PM
This is great add-in/extension for SQL 2005. I have a question but must first admit that I do not have any experience with GIS, so please keep that in mind.

I have found several SHP data sources that I would like to load into my database, however, I'm trying to determine what SRID I should use so that when I make spatial queries I get as accurate results as possible. Most of my data sources are of Pennsylvania (USA) and that region seems to be divided - North and South; or at least, that's what appears in the ST.SPATIALREFSYS table. I can request my data be projected across several different projects, but I'm not sure if North/South needs to be taken into consideration.

Also, I am Geocoding our data using Microsoft's MapPoint web service to get the latitude,longitude values for locations. Will my spatial queries such as ST.Contains or ST.Within work with lat/long pairs from MapPoint since I do not know what projection they are using?

Finally, a simpilier question: I have a polygon loaded into a table. I calculated the area using ST.Area and the result was not a double, rather < 1.0. How would I calculated the area in feet, miles, or meters?

Thanks so much for your help!!!
Feb 7, 2007 at 11:53 PM
Edited Feb 8, 2007 at 12:09 AM

I have found several SHP data sources that I would like to load into my database, however, I'm trying to determine what SRID I should use so that when I make spatial queries I get as accurate results as possible. Most of my data sources are of Pennsylvania (USA) and that region seems to be divided - North and South; or at least, that's what appears in the ST.SPATIALREFSYS table. I can request my data be projected across several different projects, but I'm not sure if North/South needs to be taken into consideration.


In general, shapefiles come with a .PRJ file, which contents are most of the times OGC compatible. You have to find the better match between .PRJ text and ST.SPATIAL_REF_SYS.SRTEXT field, thus you may get the proper SRID. If .PRJ is not present, you should contact the data provider and ask them directly.


Also, I am Geocoding our data using Microsoft's MapPoint web service to get the latitude,longitude values for locations. Will my spatial queries such as ST.Contains or ST.Within work with lat/long pairs from MapPoint since I do not know what projection they are using?


Suppose you figure out the SRID of the data is 1234. The SRID of the lat/long pairs commonly used (geographic coordinates, datum WGS-84) is 4326. To find which county contains a point:
SELECT *
FROM ST.RelateQuery#dbo#counties#the_geom(
ST.Transform(ST.GeomFromText('POINT(70.234 33.567'), 4326), 1234), 'Contains') 


Finally, a simpilier question: I have a polygon loaded into a table. I calculated the area using ST.Area and the result was not a double, rather < 1.0. How would I calculated the area in feet, miles, or meters?


SELECT ST.Area(ST.Transform(counties, 9999))
FROM counties
Where 9999 is the SRID of a coordinate system that uses the units you want to get (eg. meters).

Best regards,
Ricardo Stuven.
Mar 16, 2007 at 3:11 PM
Ricardo:

What are the RSID for the following projections:
The plate carree projection
The Mercator projection
Lambert conformal conic projection.

Thanks
Adrian
Mar 16, 2007 at 9:47 PM
As I said before, you have to find the better match in the ST.SPATIAL_REF_SYS.SRTEXT field. For example, try a query like:
SELECT *
FROM ST.SPATIAL_REF_SYS
WHERE SRTEXT LIKE '%lambert_conformal_conic%'
Surely, you will get hundreds of matches, varying on the datum and other parameters. It's up to you to choose the right one.

More info about projections supported in MsSqlSpatial/NTS/SharpMap "family" :) see
this page.

Best regards,
Ricardo Stuven.
Mar 16, 2007 at 10:33 PM
Thanks Ricardo. I have tried all three types and I got big number somewhere from 8 to 53 records and I will keep checking some other parameters.

An off - topic question:

Do you expect or see if there is any chance that Microsoft will endorse this project and add into SQLServer natively?

Mar 17, 2007 at 12:39 AM
You specifically have to look at the Datum and the Parameter values in the Projection String.
Simply put, dhe Datum defines the "shape" of the earth and the parameters define the parameters for the projection - ie. FalseEasting/Northing (offsets), central meridian (usually Greenwich) etc..

With regards to whether MS wil endorse this, don't count on it. MS is already working on their own spatial extensions for SqlServer (and about time if you ask me - everyone else has this support natively). Hopefully this will also mean that the MsSql .NET MSSQL client libraries will support a managed geometry object model.
Mar 19, 2007 at 4:11 PM
Apr. 07, 2006 - CRN.com - Paul Flessner, Microsoft senior vice president of data and storage platforms:


Flessner expanded on the company's data-and-storage vision, saying that the data store of the future must handle sound and graphical data types as well as the more standard relational and non-relational text information.

... Microsoft's plan for a unified store to handle all these data types is still on, but timing is unclear. He expects more and more data--including satellite information-- to flow into stores from sensors and that will enable creation of richer applications.

"You will see an investment in spatial indexing, geometry libraries. I want to do a good job supporting ESRI and other geo-spatial guys and make a good library available so if you can't afford those packages do good spatial analysis with out them," Flessner said.

...When will that happen? The next four to six years, although he'd like to get more done "sooner rather than later."


That "unclear timing" was one of the main motivations behind MsSqlSpatial project.

Best regards,
Ricardo Stuven.
Mar 20, 2007 at 4:51 PM
Thanks Ricardo for doing such a wondeful work.

Adrian
Apr 7, 2007 at 1:08 AM
Nice job on MsSqlSpatial..

I'd like to Transform coordinates but I'm having trouble. Looking a one coordinate pair. I would think the transform would be easy.. turns out I was wrong.. maybe I'm missing something??

- Andy


-- ESRI Transform for my input test
--> NAD1983HARNStatePlaneArizonaCentralFIPS0202Feet_Intl
-- dd: -110.99537 32.21563 = ft: 984923.1 443399.1

-- MsSqlSpatial
select st.x(geom),st.y(geom) from (
select geom =
ST.Transform( ST.GeomFromText('POINT( 984923.1 443399.1 )', 2868) , 4326)
) t
--Returns: -108.797329180134 34.9583360237288

select st.x(geom),st.y(geom) from (
select geom=
ST.Transform( ST.GeomFromText('POINT(-110.99537 32.21563 )', 4326) ,2868)
) t
--Returns: 786844.803188649 135147.80629036

oddly ... 135147.80629036 looks real close to 443399.1 *.3048


-- ESRI Projection File .prj
PROJCS["NAD_1983_HARN_StatePlane_Arizona_Central_FIPS_0202_Feet_Intl"
,GEOGCS["GCS_North_American_1983_HARN"
,DATUM["D_North_American_1983_HARN"
,SPHEROID"GRS_1980",6378137.0,298.257222101]
,PRIMEM"Greenwich",0.0
,UNIT"Degree",0.0174532925199433]
,PROJECTION"Transverse_Mercator"
,PARAMETER"False_Easting",700000.0
,PARAMETER"False_Northing",0.0
,PARAMETER"Central_Meridian",-111.9166666666667
,PARAMETER"Scale_Factor",0.9999
,PARAMETER"Latitude_Of_Origin",31.0
,UNIT"Foot",0.3048]