3

I am trying to get a Geometry query to work. A similar Geography query works fine but I must work with a table that uses a Geometry type. Although the Geography version returns lots of records as expected, I cannot get the Geometry version to return any records. Both tables have exactly the same latitude and longitude records .

This Geography query works fine:

DECLARE @home GEOGRAPHY
SET @home = GEOGRAPHY::STPointFromText('POINT(-0.7799193 51.3083162 )', 4326);

SELECT OutwardCode, InwardCode, Latitude, Longitude
FROM dbo.PostCodeData
WHERE GeoLocation.STDistance(@home) <= (5 * 1609) -- 1609 = approx metres in 1 mile

The table schema is:

+-------------+--------------+
|    Field    |     Type     |
+-------------+--------------+
| OutwardCode | Varchar(4)   |
| InwardCode  | Varchar(3)   |
| Latitude    | Decimal(9,6) |
| Longitude   | Decimal(9,6) |
| GeoLocation | Geography    |
+-------------+--------------+

Example Table Data:

+-------------+------------+------------+----------+------------------------------------------------+
| OutwardCode | InwardCode | Longitude  | Latitude |                  GeoLocation                   |
+-------------+------------+------------+----------+------------------------------------------------+
| GU14        | 9HL        | -0.7803759 | 51.30818 | 0xE6100000010C01A4367172A7494027C522E1D6F8E8BF |
+-------------+------------+------------+----------+------------------------------------------------+

This Geometry query returns no records (I have exactly the same latitude and longitude records in the database but have Geometry as a centre point for the street and Postcode is a joined version of OutwardCode and InwardCode):

DECLARE @home GEOMETRY
SET @home = GEOMETRY::STPointFromText('POINT(51.3083162 -0.7799193)', 0);

SELECT Postcode, Latitude, Longitude
FROM dbo.OS_Locator
WHERE Centre.STDistance(@home) <= (5 * 1609) -- 1609 = approx metres in 1 mile

The table schema is:

+-----------+--------------+
|   Field   |     Type     |
+-----------+--------------+
| Postcode  | nvarchar(10) |
| Latitude  | Decimal(9,6) |
| Longitude | Decimal(9,6) |
| Centre    | Geometry     |
+-----------+--------------+

Example Table Data:

+----------+-----------+-----------+------------------------------------------------+
| Postcode | Latitude  | Longitude |                     Centre                     |
+----------+-----------+-----------+------------------------------------------------+
| GU14 9HL | 51.308304 | -0.779928 | 0x346C0000010C00000000549C1D410000000018330341 |
+----------+-----------+-----------+------------------------------------------------+

Where am I going wrong?

1

This is because Geography is designed to handle coordinates in degrees (lat/long) and "knows" how to convert these into metres for your STDistance method. Geometry, however, assumes you are on a flat plane and therefore treats your map units as simple distances - so you are asking for all points within 5000 degrees of the centre and since that exceeds the surface of the sphere it probably crashes quietly. To successfully use geometry you will need to reproject your postcode centroids into a local projection, in this case I would use EPSG:27700 (OSGB National Grid). In fact I'm surprised your original data set didn't come in that to start with.

  • Many thanks. I used a bulk import SQL Script I found on the web to get the Ordinance Survey data into SQL Server. I see the script did the conversion to GEOMETRY. I ended up creating a new GEOGRAPHY type and running another script to convert the latitude and longitude to that GEOGRAPHY type accordingly. All works fine now. Cheers. – Chris Currie Jun 5 '15 at 7:44

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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