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?
There's a reason that there are two distinct geospatial types. Geography is for when your coordinates represent degrees of latitude and longitude for points on Earth (a three-dimensional object with singularities in its coordinate system). Geometry is for when your points represent arbitrary X & Y positions on an infinite plane. You can't just say "I'll take 51.3 degrees and make it the X coordinate" and have everything just work out. As my high school physics teacher always said "units are important".