[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Determine Country by Geographic Coordinate

Posted on 2009-04-17
5
Medium Priority
?
559 Views
Last Modified: 2012-05-06
Hi, is it possible to determine country name by having only geographic coordinate in SQL Server 2008?
0
Comment
Question by:zstapic
  • 3
  • 2
5 Comments
 
LVL 12

Expert Comment

by:wht1986
ID: 24170747
if you had a table containing the geography of each country it is quite easy
0
 
LVL 12

Accepted Solution

by:
wht1986 earned 1500 total points
ID: 24171193
The code to determine  what polygon a point contains might look like

select countyname from Countries where geo.STContains( geography::Point(long,lat,4326) ) = 1

It is important to remember the geography data type can only hold data which geography instance is in a single hemisphere.  Joy to microsoft for the implementation.  Heres a link that describe it further

http://blogs.msdn.com/isaac/archive/2009/02/06/the-geography-hemisphere-limitation.aspx
0
 
LVL 15

Expert Comment

by:oobayly
ID: 24171420
The NOAA provides coastline data, unfortunately it's just a series of points. While it's simple to create a geography object from them, it doesn't provide the name of the country.

http://rimmer.ngdc.noaa.gov/coast/

Also have a look here:
http://www.collinssoftware.com/freegis_by_region.htm
0
 
LVL 12

Expert Comment

by:wht1986
ID: 24190503
did that help zstapic?
0
 
LVL 15

Expert Comment

by:oobayly
ID: 24192465
I also have a basic set of data that contains the bounding rectangle for countries, you could either test for a point being inside the rectangle, or a point closest to the rectangle's centroid.
However it's not guaranteed as if you take a point just inside the border of a large country, the centroid of the neighbouring small country may be closer, so the wrong country could be returned.
0

Featured Post

New feature and membership benefit!

New feature! Upgrade and increase expert visibility of your issues with Priority Questions.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

It seems a simple enough task, yet I see repeated questions asking how to do it: how to pass data between two forms. In this article, I will show you the different mechanisms available for you to do just that. This article is directed towards the .N…
More often than not, we developers are confronted with a need: a need to make some kind of magic happen via code. Whether it is for a client, for the boss, or for our own personal projects, the need must be satisfied. Most of the time, the Framework…
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…
Suggested Courses
Course of the Month18 days, 5 hours left to enroll

830 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question