This project is read-only.

Getting Started

0. See Requirements.

1. Download the last released version of MsSqlSpatial and unzip it where you want. You can also download the latest source code and build it using Visual Studio 2005, SharpDevelop 2.x or simply MsBuild.

2. Enable SQL CLR integration. See method 1 and method 2.

3. Deploy in SQL Server 2005 using the MsSqlSpatial Command Line Tool, msscmd.exe.
  • Deploy logged in with Windows authentication (just omit -login and -password options):
msscmd -deploy -server=mssql_server -db=mssql_database
  • Deploy logged in with SQL Server authentication:
msscmd -deploy -server=mssql_server -db=mssql_database -login=username password=password

The -deploy_permission option:
This option sets the assembly permission value which can be either SAFE, EXTERNAL_ACCESS or UNSAFE. UNSAFE is not needed by the moment, but the stored procedures for importing/exporting will require external access permission at least. I you have enough privileges on the 'master' database, an asymmetric key and a granted login will be created automatically for you (see more details here). If you don't have enough privileges, you will need to use SAFE.
By default, deployment permission is set to SAFE.

4. Import spatial data. msscmd -server=mssql_server -db=mssql_database -table=mssql_table -import=shp -shp_filename="C:\..\..\shapefile.shp"
  • Import from shapefile using the ST.ImportFromShapefile stored procedure:
EXECUTE ST.ImportFromShapefile 'C:\..\..\shapefile.shp', '', 'table', 'column', -1
  • Import from PostGIS table:
msscmd -server=mssql_server -db=mssql_database -table=mssql_table -import=pgsql -pgsql_password=source_password -pgsql_db=source_db -pgsql_schema=source_schema -pgsql_table=source_table

5. Try some queries.
  • Filtering by bounding box:
SELECT t.* 
FROM ST.FilterQuery('GEOTABLE', 'GEOCOLUMN', ST.MakeEnvelope(1000,1000,2000,2000,-1)) AS q
INNER JOIN GEOTABLE AS t ON q.oid = t.oid

-- Or the equivalent:

SELECT * 
FROM ST.FilterQuery#dbo#GEOTABLE#GEOCOLUMN(ST.MakeEnvelope(1000,1000,2000,2000,-1))

  • Filtering by intersection pattern:
SELECT t.* 
FROM ST.RelateQuery('GEOTABLE', 'GEOCOLUMN', ST.GeomFromText('POLYGON((1000 1000, 2000 2000, 3000 1000, 1000 1000))',-1), 'Contains') AS q
INNER JOIN GEOTABLE AS t ON q.oid = t.oid

-- Or the equivalent:

SELECT * 
FROM ST.RelateQuery#dbo#GEOTABLE#GEOCOLUMN(ST.GeomFromText('POLYGON((1000 1000, 2000 2000, 3000 1000, 1000 1000))',-1), 'Contains')


(Instead of 'Contains' you can also use 'CoveredBy', 'Covers', 'Crosses', 'Disjoint', 'Equals', 'EqualsExact', 'Intersects', 'Overlaps', 'Touches', 'Within' or any DE-9IM pattern.)
  • Distance queries:
SELECT t.* 
FROM ST.IsWithinDistanceQuery('GEOTABLE', 'GEOCOLUMN', ST.GeomFromText('POINT(1000 1000)',-1), 100) AS q 
INNER JOIN GEOTABLE AS t ON q.oid = t.oid

-- Or the equivalent:

SELECT * 
FROM ST.IsWithinDistanceQuery#dbo#GEOTABLE#GEOCOLUMN(ST.GeomFromText('POINT(1000 1000)',-1), 100) 

  • Spatial join queries:
SELECT m.name, sum(ST.Length(r.the_geom))/1000 AS roads_km 
FROM ST.JoinFilterQuery('bc_roads','the_geom','bc_municipality','the_geom') AS q 
INNER JOIN bc_roads AS r ON q.oid1 = r.oid
INNER JOIN bc_municipality AS m ON q.oid2 = m.oid
WHERE ST.[Contains]( m.the_geom,r.the_geom)=1 
GROUP BY m.name 
ORDER BY roads_km 


6. Explore all the functions and stored-procedures deployed under the ST schema. If you have some doubt, comment or catch a bug, please drop a message in the discussions board or report the issue.

Last edited Jun 20, 2007 at 6:31 PM by rstuven, version 11

Comments

upster Nov 25, 2007 at 10:38 AM 
Step 4 asks to import spatial data using file shapefile.shp. That file is not present in the 0.1.1 release. Where do I get it? Thanks.

rstuven Jan 31, 2007 at 5:21 PM 
Argument names are fixed.

CraigN Sep 11, 2006 at 3:23 PM 
Note that the command line examples above are errored as they reference argument names with a period (.) in them and they should be an underscore (_).