Trying to create a heatmap in SQL but there is something off about my Spatial data

Question

I am trying to create a heatmap of customer orders across states. I am following this tutorial:

https://www.mssqltips.com/sqlservertip/3550/sql-server-reporting-services-analytical-marker-maps-including-bing-maps/

However, for some reason, the points do not go to the correct place.

I have a table full of customers and their city and state. To get the longitude and latitude of each city, I downloaded a database from here https://simplemaps.com/data/us-cities

I then joined that database to my customer information to get the longitude and latitude for each customer. Then I mapped that to a SpatialLocation.

Here is my query:

SELECT TOP 100
    --*
    --,CAST('POINT( ' + right(left(LAT, len(LAT) -1),len(LAT) -2) + ' ' + right(left(LNG, len(LNG) -1),len(LNG) -2) + ' )' as geometry) AS SpatialLocation 
    CSS.City
    ,CSS.State
    ,CSS.TotalCustomerValue
    ,CSS.TotalOrders
    ,CSS.OrderDensityPercent
    ,geography::STPointFromText('POINT(' + CAST(right(left(LAT, len(LAT) -1),len(LAT) -2) AS VARCHAR(20)) + ' ' + CAST(right(left(LAT, len(LAT) -1),len(LAT) -2) AS VARCHAR(20)) + ')', 4326) SpatialLocation2
    ,CSS.BrandNewCustomers
    ,CSS.RecurringCustomers
    ,CSS.ReactivatedCustomers
FROM
    (SELECT DISTINCT
        --*
        LTRIM(RTRIM(cs.City)) City
        ,LTRIM(RTRIM(cs.State)) State
        ,RIGHT(LEFT(lat, len(lat) -1),len(lat) -2) lat -- the lat and long are wrapped in quotes
        ,RIGHT(LEFT(lng, len(lng) -1),len(lng) -2) lng -- so i have to do the left right to get rid of them. 
        ,SUM(cs.TotalCustomerValueOverBase) over (partition by  cs.City, cs.State) TotalCustomerValue
        ,SUM(cs.TotalOrdersBase) over (partition by  cs.City, cs.State) TotalOrders
        --,SUM(cs.TotalQuantityOverBase) over (partition by  cs.City, cs.State) TotalQuantity
        ,right(left(population_proper, len(population_proper) -1),len(population_proper) -2) population_proper
        ,CAST(
            SUM(cs.TotalOrdersBase) over (partition by  cs.City, cs.State) 
            /
            NULLIF(convert(float, right(left(population_proper, len(population_proper) -1),len(population_proper) -2)),0)*100 
        as decimal(10,2)) AS OrderDensityPercent
        ,SUM(cs.BrandNewCustomer) over (partition by     cs.City, cs.State) BrandNewCustomers
        ,SUM(cs.RecurringCustomer) over (partition by    cs.City, cs.State) RecurringCustomers
        ,SUM(cs.ReactivatedCustomer) over (partition by  cs.City, cs.State) ReactivatedCustomers
    FROM 
        Customers -- This table gives full customer information per customer.
    LEFT JOIN 
        [A1Warehouse].[dbo].[uscities] Ci ON cs.City = right(left(ci.city_ascii, len(ci.city_ascii) -1),len(ci.city_ascii) -2) and cs.State = right(left(ci.state_id, len(ci.state_id) -1),len(ci.state_id) -2)
    WHERE 
        LAT IS NOT NULL AND LNG IS NOT NULL) CSS
ORDER BY 
    TotalCustomerValue DESC

Here is a sample result:

City    State    Amount    Orders    PopDensity    SpatialLocation
Brooklyn    NY  $663    8535    0.32    0xE6100000010CCDCCCCCCCCCCE43FCDCCCCCCCCCCE43F
MIAMI   FL  $529    7042    1.52    0xE6100000010C894160E5D0221740894160E5D0221740
Bronx   NY  $443    5901    0.41    0xE6100000010C333333333333EB3F333333333333EB3F

How do I know where this is going wrong? Is the spatial location wrong or is ssrs displaying them wrong? I checked a few customer longitude and latitude coordinates and they seemed right, I am not sure how to check spatiallocation.

score 1 · Answer 1 · answered Jun 20 '19 at 19:57

The Open Geospatial Consortium (OGC) Well-Known Text (WKT) representation, the format is (x, y). Geographic coordinates are usually specified by Lat/Long but between these two, the X is the Longitude while the Y is the Latitude. http://www.sql-server-helper.com/sql-server-2008/convert-latitude-longitude-to-geography-point.aspx

So the longitude should be specified first before the latitude and correct geography::STPointFromText arguments order is

...
    ,geography::STPointFromText('POINT(' + CAST(right(left(Lng, len(Lng) -1),len(Lng) -2) AS VARCHAR(20)) + ' ' + CAST(right(left(LAT, len(LAT) -1),len(LAT) -2) AS VARCHAR(20)) + ')', 4326) SpatialLocation2
...

I tried that, it still doesn't work. That was one of the first thing I tried, switching the lat and ln. It just made the map incorrect in an opposite way. — Natan, Jun 21 '19 at 12:52

score 0 · Answer 2 · answered Jun 26 '19 at 13:24

The problem was that I was doing the left, right thing on the latitude and longitude causing it to be completely messed up. Doing it once was correct, since I needed to remove the quotes that surrounded it, but I did it a second time when I was creating the geography field, which was removing the first and last character of a correctly formatted location point.

Trying to create a heatmap in SQL but there is something off about my Spatial data

2 Answers2