Spatial Data - finding all cities in the coridor

Question

Hi,

I have a Cities geo data table with Lat and Long columns in it. I want to find all cities which fit into the 50 miles coridor between City1 and City2 (say Seattle to LA).
What is the best way to do it? Thanks.

Answer 1

Hi,

When you desribe a "Corridor" between two cities, do you mean the straight line that connects those two cities (as the crow flies), or do you mean following the line following the route of a major road that connects those cities? Assuming that you mean the straight line route, firstly you need to define a LineString that connects the two cities. Using the example of Seattle and LA, this would be something like this:

DECLARE @Corridor geography = geography::STLineFromText('LINESTRING(-122.3 47.6, -118.2 34.0)', 4326);

Next, you want to define the size of the buffer (i.e. the thickness of the corridor). The spatial reference system I'm using uses linear units measured in metres, so if you want the buffer to include cities that lie within 50 miles of the route, that means a buffer of 80467 metres.

DECLARE @BufferedCorridor geography = @Corridor.STBuffer(80467);

Once you've defined the buffer zone in which to search for cities, you select those records that lie in the search zone using STIntersects():

SELECT
  name,
  location
FROM
  allCountries WITH(INDEX(idxallCountries))
WHERE
  location.STIntersects(@BufferedCorridor) = 1;

(you might need the hint to get the index on this table to be used)


This gives you the results something like as follows:




If instead of the straight line, you want to follow the road route between the two cities, you need to use a route-finding service that returns a linestring repesenting the route, and then buffer that instead. I wrote an article describing how to do this using the Virtual Earth Web Service, here:
http://www.viawindowslive.com/Articles/VirtualEarth/CreatingRoutesinSQLServer2008usingVE.aspx

---

Beginning Spatial with SQL Server http://www.apress.com/book/view/1430218290

Answer 2

Following the road route between Seattle and Los Angeles, for anybody interested, would give the following results:

---

Beginning Spatial with SQL Server http://www.apress.com/book/view/1430218290

Answer 3

Thanks a lot tanoshimi !!! This is exactly what I was looking for.

A couple of questions for you:

1. Could you please send me a schema for allCountries table?
    What is the datatype for location column and what if we have two separate columns - lattitude and longitude?
    How would this map to quiery ?

2. Could you clarify WHERE clause - WHERE location.STIntersects(@BufferedCorridor) = 1;
    is Intersects = 1 a condition to be inside the corridor? ASre there any other ways to specify a condition to be inside.
    And again - what if location column is in fact two columns (lat and long)

Thanks again for your help!.  

Answer 4

Hi there,

1.) my allCountries table is loaded with the allCountries data export from geonames - freely available from http://www.geonames.org/export/
The location column is of the geography datatype, which is what you'll want to use to define data using coordinates of latitude and longitude.

2.) If you use the geometry or geography datatypes rather than store separate columns of latitude and longitude, then you'll be able to access dedicated methods such as STIntersects() to do spatial analysis.
- STIntersects() tests whether two instances intersect (that is, they have at least one point in common).
- STContains() and STWithin() test whether one instance is totally contained within another
- STCrosses() tests whether one instance crosses another
- STTouches() tests whether two instances touch
If you store your data using separate floating points in two different columns, you'll have to write your own queries to perform this functionality.

Note - you can only use the geography datatype if you're running SQL Server 2008... I assume you are, but you didn't say.

---

Beginning Spatial with SQL Server http://www.apress.com/book/view/1430218290

Answer 5

Thanks for prompt response tanoshimi,

Yes I am using SQL Server 2008 now and this is the primary reason why I am interested in geography datatype (and in spatial features of SQL Server 2008).
I have a custom-baked geo functionality implemented in my old application (SQL Server 2000) and I am trying to migrate it now to SQL Server 2008.
This is the main explanation why my Cities Table (freely available from MaxMind (http://www.maxmind.com/app/geolitecity) has two separate columns - one for Latitude and another one for Longitude.

Could you point me to the link which explains how to migrate that old table with two columns into the new one with geography datatype? 

Thanks again, your assistance is very much appreciated.

Answer 6

Ok, so if your table currently looks like this:

CREATE TABLE OldTable (
  Placename varchar(32),
  Latitude float,
  Longitude float
  );
  
INSERT INTO OldTable VALUES ('London', 51.5, -0.5);

Then you can add a new geography column to the table as follows:

ALTER TABLE OldTable
ADD Location geography;

Then you can populate the new Location column with geography Point instances from your existing latitude and longitude columns, using the Point() method:

UPDATE OldTable
SET Location = geography::Point(Latitude, Longitude, 4326);

 

---

Beginning Spatial with SQL Server http://www.apress.com/book/view/1430218290

Answer 7

I have eventually figured it out and went similar but a little longer way with a query below which creates a Cities_USA_new table on-the-fly: 

SELECT [CityName], [Latitude], [Longitude], [Region], [CountryCode]
,<strong>GEOGRAPHY::STGeomFromText('POINT(' + CAST(ct.Longitude AS varchar(100)) + ' ' + CAST(ct.Latitude AS varchar(100)) + ')', + 4326) As GeoLocation</strong>
INTO [dbo].[Cities_USA_new]
FROM [dbo].[Cities_USA_old] ct
WHERE CountryCode = 'us'

Note the expression:GEOGRAPHY::STGeomFromText('POINT(' + CAST(ct.Longitude AS varchar(100)) + ' ' + CAST(ct.Latitude AS varchar(100)) + ')', + 4326) As GeoLocation. It looks ugly but it works !!!

Note also that my attempt to pass Lat Long values directly into GEOGRAPHY::POINT(ct.Longitude,ct.Latitude,4326) in SELECT statement didn't work out for me (althougth your code above works fine);

 Msg 6506, Level 16, State 10, Line 14

Could not find method 'POINT' for type 'Microsoft.SqlServer.Types.SqlGeography' in assembly 'Microsoft.SqlServer.Types'

Answer 8

Yes - that will work too - you're just using string functions to build a WKT representation of a point and then create that from STGeomFromText. You'll get the same end result, it will just be a bit slower.

Judging from your error message, I'd say that the reason why you couldn't get my method to work was that you were trying to use geography::POINT rather than geography::Point
Remember that geography and geometry are CLR UDT datatypes, so all methods are case-sensitive...

---

Beginning Spatial with SQL Server http://www.apress.com/book/view/1430218290

Answer 9

Thanks again tanoshimi!

I was totally under the impression that these spatial stuff is a pure SQL and thus - is case-insensitive. Now I can recall that they are CLR UDT data types.

Yes, your version works now flowlessly and it runs must faster that "string-based" vesrion based on WKT.

I have also noticed that the order of arguments in the functions call, i.e Lat, Long vs. Long, Lat is not the same in all scenarios. And I even remember that there was somewhere mentioning that the order was changed in final release of SQL Server 2008. Could you share your expertise here? 

Thanks

Answer 10

Basically, the Well-Known Text format, which is a cross-platform standard administered by the Open Geospatial Consortium, always gives geographic coordinates in Longitude - Latitude order. So, every time you see a method that is STxxxxFromText() in SQL Server, this is the format it expects. This is true for individual points, and the points in linestrings or polygons etc. This order actually makes a lot of sense, because in mathematics you normally list X coordinates followed by Y coordinates, and the geographic coordinates of longitude and latitude are mostly closely related with X and Y respectively. So, X-Y ordering -> Long-Lat ordering.

However, in common English language, many people are familiar with saying "Latitude/Longitude" coordinates, so the extended methods provided by SQL Server that are not OGC-compliant, (notably, the Point() method), use Latitude-Longitude ordering instead.

---

Beginning Spatial with SQL Server http://www.apress.com/book/view/1430218290

Answer 11

Thanks again !

One final (for this thread :=) ) question.

You have nicely put query output into the Map.
I have read your artcile and know that it could be integrated with VE for professional use.

Is there any simple way to get this displyed without heavy coding - I need this just for convenience of observing results.
 
Thanks

Answer 12

There are lots of ways to integrate SQL Server 2008 with "front-end" visualisation tools such as Bing Maps or Google Maps, but unfortunately, they all require some coding - if you just want a quick visualisation then that's what the Spatial Results tab in SQL Server Management Studio is for, which is what I used for the screenshots above.

If you do want to display data on Bing Maps, I find the easiest way is to create a GeoRSS feed of the data, which can then be imported straight into a new shape layer using VEShapeSourceSpecification. You can do this all in a single stored procedure by using AsGml() to get the GML representation of each point/linestring/polygon, and then using XQuery or XSLT to apply the necessary styling and elements to make a valid georss feed (as described at http://georss.org). Then, you just need about 10 lines of HTML/Javascript code to build a web page that connects to SQL Server, executes the stored procedure, and plots the resulting GeoRSS on the map.

If you want more information, I describe this process in detail together with all required code in Chapter 8 of "Beginning Spatial with SQL Server 2008"

---

Beginning Spatial with SQL Server http://www.apress.com/book/view/1430218290

Answer 13

Thanks!