Different ways to add point data in SQL Server 2008


The spatial data can be added to a table by specifying the column type of geometry or geography. The exact detail of what is in the column can be varied as a spatial column can represent a point, line string, and polygon and so on. For example, to create a table that represents the venues of developer events that I’ve been to might look like this:

    Name NVARCHAR(256),
    Location geography

There are a number of different ways to insert data. Points, have the most varied set of options.

First of all there is the standard STGeomFromText:

        'POINT(55.9271035250276 -3.29431266523898)',4326));

The function takes two parameters; the first is the Well Known Text (WKT) representation of the geometry, in this case a point, and the second is the SRID. The example above shows the location of the SQL Server UG events held in one of the conference rooms at HBOS’s offices in Sighthill, Edinburgh.

Next is the extended method Parse. I should mention that there are two types of methods with regards to standards. There are a group of methods that comply with the OGC standards (these are all prefixed with ST). Then there are “extended methods”. These are not standards compliant and have added, I’m guessing, in order to improve the capabilities to some extent over the standards.

An example of the Parse method:

    'Glasgow Caledonian University',
        'POINT(55.8659449685365 -4.25072511658072)'));

The function takes only one parameter, which is the WKT. There is no SRID, but it is set to 4326 (WGS84). The example above shows the location of the Scottish Developers events held in the Continuing Professional Development Centre in Glasgow Caledonian University.

The next method is to use Well Known Binary (WKB). I won’t, however, be detailing the format of the binary. At present I would simply like to demonstrate that it can be done.

An example of WKB:

    'Dundee University',
    geography::STGeomFromWKB(0x01010000000700ECFAD03A4C4001008000B5DF07C0, 4326));

The function, like its WKT counterpart, takes two parameters. The first is the binary representation of the spatial data, while the second is the SRID. The example above is the location of the North East Scotland .NET User Group who meet at Dundee University.

Next is another extended method, Point. For example:

    'Microsoft Campus, TVP',
    geography::Point(51.4618933852762, -0.926690306514502, 4326));

The function takes three parameters, the latitude, the longitude and the SRID. The above example is the location of the Microsoft Campus at Thames Valley Park in Reading where events like DDD are held.

Finally, the standard function, STPointFromText, is used. For example:

    'Microsoft Edinburgh Office',
    geography::STPointFromText('POINT(55.9523783996701 -3.2051030639559)', 4326));

The function takes WKT as did Parse and STGeomFromText, however, it is constrained to only WKT that represent points. If the WKT represents something else the method will fail. If, say, a linestring was supplied then an error message would be generated such as “Expected POINT at position 1. The input has LINES.” The example above shows the location of Microsoft’s Edinburgh office.

The result of adding all this information will produce a table with the following data:

Id Name Location
1 HBOS POINT (55.9271035250276 -3.29431266523898)
2 Glasgow Caledonian University POINT (55.8659449685365 -4.25072511658072)
3 Dundee University POINT (56.4595025684685 -2.98423195257783)
4 Microsoft Campus, TVP POINT (51.4618933852762 -0.926690306514502)
5 Microsoft Edinburgh Office POINT (55.9523783996701 -3.2051030639559)




2 thoughts on “Different ways to add point data in SQL Server 2008

  1. The Point function and the POINT macro appear to take longitude and latitude params in a different order! Try below:

    DECLARE @g1 geography;
    DECLARE @g2 geography;
    SET @g1 = geography::STGeomFromText('POINT(55.86 -4.25)', 4326);
    SET @g2 = geography::Point(55.86, -4.25, 4326);
    select @g1.Lat
    select @g2.Lat

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s