9

I've been working on opening up our spatial data from a proprietry system to one that can be read by more products (MapGuide, ESRI, gvSIG, Map3D etc....)

I've decided on using SQL Spatial as we have a server in house available to us and we can link to other systems easily enough through it.

Before I go all out in actually using it as our central datastore is there anything I should be aware of that could greatly decrease performance?

Improve this question

4 Answers 4

Reset to default
6

My answer is not about performance, but do be aware that you are limited to Simple Features, and a limited set of the Spatial SQL queries. Not that this is necessarily a bad thing, although I have quickly found my self wanting of some of the SQL queries available in say Postgres or Oracle. The simple features I actually like, keeping it simple lets you follow good database practices, and lets you transform your data into lines, polygons, relationships, what ever you want.

Improve this answer
1
  • 2
    Latest release allows for curves/circular arcs and the geography type now has all the spatial functions that geometry has
    – geographika
    Commented Nov 14, 2010 at 21:16
5

Uses Multi-grid indexing rather than R-Tree as PostGIS and Oracle.

Not performance related but maybe important:

Does not support coordinate transformations.

There is a slight difference in SQL syntax. Example:

SELECT * FROM table1 WHERE the_geom.STIntersects(geometry::STGeomFromText('POINT(100 100)',0));

There are probably some more but currently i cant remember them :)

Improve this answer
5

A few negatives:

  • as mentioned by Mario mentioned no inbuilt projection tools mean additional software (FME or GDAL are useful) are required to reproject data

  • performance is lacking for some spatial queries (intersects / within), and spatial indexes have to be created manually, although in the next Denali release there have apparently been vast improvements in performance, and "auto" spatial indexes

  • no linear referencing (but can be added with .NET code - see below)

  • lack of community - there is one related opensource project at http://sqlspatialtools.codeplex.com/ with little activity so drivers and tools are at the mercy of Microsoft releases. Not too many SQL examples.

  • MapServer and GDAL now both have SQL Server 2008 drivers, but these have only come out recently - several years after other spatial databases.

On the plus side:

  • integration with .NET. As SQL Server allows .NET code to be run in the database it allows for funcitonality in .NET DLLs and libraries to be included in views, stored procedures, triggers etc. Libraries such as http://projnet.codeplex.com/ can be included to allow reprojections in the database.

  • proprietary systems all include SQL Server drivers / loaders etc.

  • many organisations already have SQL Server DBAs, servers, backup processes

  • the SQL Server Management Studio is a very nice tool, and includes spatial visualisations

  • OGC standards for spatial methods and simple features

Improve this answer
3

If your data is stored as geography type on a global scale you need to be aware of the Hemisphere Limitation.

Improve this answer
2
  • Thanks Kirk the data will be stored as geometry, so this shouldn't be an issue, the limits of the project area are relatively small, so far I have succsessfully brought across some 36,000 features into sql (utilities, cadastre and other various featuers)
    – Jamo
    Commented Aug 18, 2010 at 22:32
  • The latest Denali release of SQL Server includes support for full globe spatial objects - apparently "industry firsts for relational database systems"
    – geographika
    Commented Nov 14, 2010 at 21:14

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.