locked
map translated? or whatelse? RRS feed

  • Question

  • Hi to all, i got this big problem.

    In the db of my company we've loaded many locations from the geonames with the respective coordinates.

    In these days i'm developing a store procedure that takes some bad located users (in the sea) replacing the bad coordinates with a random point taken from the linestring that intersect the map with the center of the location associated at the user.

    But there is a problem, some locations in my db got coordinates that doesn't intersect my loaded maps, so the coordinates associated at the location result to be in the sea .....

    The coordinates loaded of my locations seems to be corrected, i tested with google maps, so i supposed that my loaded map is a little translated or whatele, i don't know.

    Usually to have a good detail for maps loaded on the db, i take the 1 level administrative boundarys, join the regions to obtain one singolar state quite detailed.

    Does anyone know how to fix my problem? any ideas?

    What and where download acceptable detailed and certified maps? Even if i don't think is a problem of detail..

    Thanks Massimo

    Friday, September 24, 2010 5:25 PM

Answers

  • To get a random point from a geography:

     

    -- GEOGRAPHY
    declare @geo geography = '...'
    
    -- Get Envelope
    declare @envelope geometry = geometry::Parse(@geo.ToString())
    
    -- Get limits
    declare @x1 float = @envelope.STPointN(1).STX
    declare @x2 float = @envelope.STPointN(3).STX
    declare @y1 float = @envelope.STPointN(1).STY
    declare @y2 float = @envelope.STPointN(3).STY
    
    declare @randomPoint geography = 'POINT EMPTY'
    declare @r1 float
    declare @r2 float
    
    -- Look for point
    while (@randomPoint.STIntersects(@geo)<>1)
    BEGIN
    	set @r1 = RAND()
    	set @r2 = RAND()
    
    	set @randomPoint = geography::Point(
    		@r2*(@y2-@y1) + @y1
    		,@r1*(@x2-@x1) + @x1
    		,@geo.STSrid)
    END
    
    select @randomPoint
    

    • Marked as answer by Jossy80 Monday, October 4, 2010 9:57 PM
    Tuesday, September 28, 2010 11:39 AM
  • Hi,

    i used this solution to take a random point of a portion of circumference that intersects a map.

    DECLARE @c geography = @city_point.STBuffer(2000).RingN(1)
        DECLARE @num_points INT =@c.STIntersection(@country_map).STNumPoints()
        IF(@num_points>=1)
        BEGIN
            DECLARE @point_seed INT = CAST(@rand*100000000000000 AS BIGINT)%@num_points+1
            SET @point_return = @c.STIntersection(@country_map).STPointN(@point_seed)
        END

    ELSE.....

    and it works!

    I just want to say thank you for the support .

    thanks Massimo

    • Marked as answer by Jossy80 Monday, October 4, 2010 12:59 PM
    Monday, October 4, 2010 12:59 PM

All replies

  • A common mistake is the order of latitude and longitude.

     

    If you are creating a geography using STPoint:

    lat, lon

     

    If you are creating a geometry using STPoint:

    lon,lat

     

    If you are creating from WKT (always)

    lon, lat

    Saturday, September 25, 2010 11:38 AM
  • Hi, thanks for your reply, but this command won't work, and i can't find the definition of this istantiation method...I don't even find the differences bettween POINT and STPOINT...

     

    --This works and this is the one i'm using to create geographic points

    SELECT geography::Point(25, 25, 4326)

    --This don't works

    SELECT geography::STPoint(25, 25, 4326)

     

    Please can you explain me ?

    Thanks Massimo

    • Edited by Jossy80 Monday, September 27, 2010 8:41 AM
    Monday, September 27, 2010 8:32 AM
  • For geography, the method is called Point (without ST)

    SELECT geography::Point(25, 25, 4326)

     

     

    http://msdn.microsoft.com/en-us/library/bb933811.aspx

     

     

    Monday, September 27, 2010 8:36 AM
  • Ah ok, i'm already using it.....i don't know how to resolve the problem..

     

    i got cities near the coast that in my map results in the sea and if i put coordinates in google map results well placed..... for you the solution could be  to load a more detailed map?

    I loaded an admin 1 level map, can itry to load an admin2 map level?

    thx Massimo

    Monday, September 27, 2010 8:45 AM
  • Nothing,

    i even loaded  a second level map but nothing, the level of detail is the same of the first level...

    Monday, September 27, 2010 9:14 AM
  • Admin level 2 maps are not more accurate than level 1 maps - they are more detailed . For example, whereas Admin Level 1 might show an entire country as a single  polygon, Admin level 2 might break that country into regions, admin level 3 will show local districts etc. So, if your point is not located within a country boundary at level 1, it won't  be compared to level 2,3, or 4 either.

    Seeing as you are representing "cities near the coast" as single points, it's not entirely surprising that sometimes the location of the point representing that city lies in the sea. Indeed, I can think of many cities built around bays where the "centre" of the city (assuming that's where your point is meant to be placed) should rightly be placed in the centre of the bay - i.e. in the water. Also bear in mind that you seem to be comparing the output of Geonames and Google Maps - they're both excellent resources, but you can only expect so much accuracy considering they're both free tools!

    Perhaps we could help more if you gave us some actual examples of a point that is being incorrectly placed - if you give us the latitude/longitude we can confirm whether it's in the sea or not.

    Oh, and finally, you are using the correct SRID for your data, aren't you?


    Beginning Spatial with SQL Server http://www.apress.com/book/view/1430218290
    Monday, September 27, 2010 9:32 AM
    Answerer
  • Thanks Tanoshini :) You're exactly centered the question, these are the coordinates of Verbania (italy) that is one of the cities that gave me the isssues..

    latitude                     longitude
    45,9213635501067    8,55182647705078

     

    Yes i use the correct SRID 4326 to istantiates the points.

     

    Thanks Massimo

    Monday, September 27, 2010 9:52 AM
  • I notice that your coordinates have comma separators rather than decimal point separators - I wonder if this is what's causing the problems, because this is the result I get, which clearly shows the coordinates of Verbania you supplied in the correct location:


    Beginning Spatial with SQL Server http://www.apress.com/book/view/1430218290
    Monday, September 27, 2010 10:16 AM
    Answerer
  • Just to give you more informations...

    we're using geonames coordinates stored in db for locations info, google maps views  in the clients that use application (just to have a graphical view), and sql server with maps loaded from differnt websites, to build a store that correctly replace the bad users in the sea.

    Tanoshini, i also accept suggests to use good data resources (even not free) that are compatibles beetween themselves.

    we haven't experience in using and manipulate gis informations, i'm even the first that is getting closer to sql and its geographical functions...so i'm not much expert but i would convince the company to continue to use sql server for this problematics.

    My fear is that we are using in the wrong manner the locations informations at the origin ( bad sources of data,  few knowledge about the use of a good architecture that support GIS or bad management of data).

    Thanks

    Massimo

    Monday, September 27, 2010 10:19 AM
  • I think Tanoshimi gave the right answer (collation again!)

     

    Anyway, you can find good free spatial data at

    http://www.diva-gis.org/Data

    Monday, September 27, 2010 10:28 AM
  • I think we might have cross-posted our last two comments!

    The map I posted above shows the coordinates you provided overlaid on an administrative map of Italy I downloaded (for free) from http://www.diva-gis.org/gdata - which, incidentally, is a great resource for high level administrative maps of every country of the world.

    But I'm not sure if we've answered your question yet - i.e. is it that your point is being plotted in the wrong location (your coordinates are fine, so this must be because you're not using SQL Server's spatial functions correctly), or is that the point is in the right location, but your background maps in SQL Server are incorrect (which may either be because you've got poor quality data, or because you've loaded them incorrectly). Please compare your results to my results above and let us know!


    Beginning Spatial with SQL Server http://www.apress.com/book/view/1430218290
    Monday, September 27, 2010 10:32 AM
    Answerer
  • Massimo,

    Are you sure that your maps are in WGS84? Maybe they are European Datum 1950 (ED50)

    http://noe.cartodata.free.fr/spip.php?article290

     

    In Spain, the offset from WGS84 to ED50 is only about 250m, so at high levels seems similar but they aren't 

    Monday, September 27, 2010 10:46 AM
  • Thanks my friends for reply :)

    Tanoshimi, the map you're using is different from mine. Mine is more accured, if you try to search for verbania (italy ) in google maps, you will see that the location is in front of a lake. The lake, in your map isn't rapresented, that's why you see verbania correct placed. In my map i see the lake....

    I can give you another city that is really on the sea and not in the lake.

    TARANTO (italy)

    latitude    longitude
    40,4761111    17,2297222

    Tanoshimi, for you is a problem using tha values with comma? is the standard representation of geonames, and sql doen't give me problems...

    ps: to load any image in the forum i must register to imageshack or similar sites or have an access to a registered space? i would want to send you some grapphical examples

    thanks Massimo

    Monday, September 27, 2010 10:53 AM
  • Massimo,

    Are you sure that your maps are in WGS84? Maybe they are European Datum 1950 (ED50)

    http://noe.cartodata.free.fr/spip.php?article290

     

    In Spain, the offset from WGS84 to ED50 is only about 250m, so at high levels seems similar but they aren't

    mmm...interesting observation...how can i see what kind of maps i'm using? Other question,do you know what kind of 'coding' google maps use?

    thanks

    Monday, September 27, 2010 11:10 AM
  • Depends on your data source... if it is a shape file, for example, check the .PRJ file

     

    Google (as well as Bing Maps, Open Layers...) renders it contents using Spherical Mercator, but all the APIS get the inputs in WGS84, and do the conversion internally

    Monday, September 27, 2010 11:17 AM
  • I think we might have cross-posted our last two comments!

    The map I posted above shows the coordinates you provided overlaid on an administrative map of Italy I downloaded (for free) from http://www.diva-gis.org/gdata - which, incidentally, is a great resource for high level administrative maps of every country of the world.

    But I'm not sure if we've answered your question yet - i.e. is it that your point is being plotted in the wrong location (your coordinates are fine, so this must be because you're not using SQL Server's spatial functions correctly), or is that the point is in the right location, but your background maps in SQL Server are incorrect (which may either be because you've got poor quality data, or because you've loaded them incorrectly). Please compare your results to my results above and let us know!


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


    tanoshimi!

    for me is the second; the point is in the right location and the map loaded got a problem. In the case of verbania the map is too much detailed, show the lake and put the location in the water. BUT i could also have the opposite problem. i.e : a location in an greece island

    that is placed in the sea cause the map loaded is few detailed.....

     

    Monday, September 27, 2010 11:20 AM
  • Depends on your data source... if it is a shape file, for example, check the .PRJ file

     

    Google (as well as Bing Maps, Open Layers...) renders it contents using Spherical Mercator , but all the APIS get the inputs in WGS84, and do the conversion internally


    thanks , not all tha maps i 've loaded  have the prj file. I allways use shape2sql to load shape files (is the only way i know to load maps)...

    I will try to use the site you recomend me. In  this site, maps are always in  WGS84  format?

    thanks Massimo 

    Monday, September 27, 2010 11:30 AM
  • By the way,

    You can download "inland water" at DivaGis. It's a shape with rivers, lakes... please use to check the famous "lake". It it fits to your lake (mostly), at least you know your data is WGS84

     

    And yes, DivaGis is always WGS84

     

     

    Monday, September 27, 2010 11:31 AM
  • I think that a screenshot would really help, because all your data looks fine to me! Here's the details for Taranto:

    And you can check that it definitely intersects the shape of Italy, as follows:

    DECLARE
    
     @Taranto geography = geography::Point(40.4761111, 17.2297222, 4326);
    SELECT
    
     * FROM
    
     ITA_adm3 WHERE
    
    
    geog4326.STIntersects(@Taranto) = 1
    

    Results:

    ID 664
    ID_0 112
    ISO ITA
    NAME_0 Italy
    ID_1 1412
    NAME_1 Apulia
    ID_2 16805
    NAME_2 Taranto
    ID_3 28639
    NAME_3 Taranto
    VARNAME_3
    NL_NAME_3
    HASC_3 IT.TA.TA
    TYPE_3 Commune
    ENGTYPE_3 Commune
    VALIDFR_3 Unknown
    VALIDTO_3 Present
    REMARKS_3
    Shape_Leng 2.583381
    Shape_Area 0.02417907
    geog4326 0xE61000000107E00B

    If you can't find anywhere to host your pictures, send a screenshot to me at alastair (at) beginningspatial (dot) com and I'll put them up for you.


    Beginning Spatial with SQL Server http://www.apress.com/book/view/1430218290
    • Edited by tanoshimiEditor Monday, September 27, 2010 11:46 AM minor correction
    Monday, September 27, 2010 11:39 AM
    Answerer
  • Ok tanoshimi, i'll download maps from http://www.diva-gis.org/gdata, and i will try to work on it.

    I'll let you know asap. (however i see you've used a 3 level admin details....there's more accuracy? :))

    ps: i've sended an email with the 2 examples.

    thanks Massimo

    Monday, September 27, 2010 12:58 PM
  • I see the maps, as you told me level 3,2,1 have the same accuracy, level 0 i can't display cause sql told me the objects is too large for represent.....

    I need a level 0 details for the work so i got 2 cases:

    1)join the map of level admin 1 to obtain one row level 0,

    2)download from http://www.gadm.org/world the entire world level 0 maps

    I think i'll i'll wait  to download the files from gadm, i really hope it works, there would be a problem if i couldn't view the maps on the screen as the first example (level 0)

     

    Thanks Massimo

     

    Monday, September 27, 2010 1:37 PM
  • Pictures from Jossy80 attached:

    Verbania

    Taranto


    Beginning Spatial with SQL Server http://www.apress.com/book/view/1430218290
    Monday, September 27, 2010 1:42 PM
    Answerer
  • When your first post described points as being placed "in the sea", I assumed that we were talking about errors of miles or more. Having seen your screenshots, I can see that the discrepancy we're talking about is one of a few metres instead!

    To be honest, I think you are using SQL Server spatial features correctly, and I don't think your underlying base data is that bad either. Your problem arises as a result of the fundamental theoretical issues in defining spatial data and, in particular, the representation of an entire city as a singular "point".

    As I suggested in an earlier post, assuming you want your point to be located at approximately the geometric "centre" of a city, then there are plenty of legitimate cases where this point may lie in the sea. Taranto appears to be a clear example of this - where a settlement spans across a bay, the centre of the city may fall in the middle of the bay itself. But what do you want to do in such cases? You could move the point to be on either one side of the bay or the other, but that would no longer accurately portray the centre of the city. Or you could leave the point where it is, but then as in your case it might appear to lie in the sea. Or you could choose to represent each city as a polygon instead of a point, more accurately mapping the outskirts of the city boundary, but that would require a lot more data.

    All spatial data is, by definition, an approximation. Nothing on earth is really a singular point, nor a straight linestring, nor a simple angular polygon. One of the key things to get used to when handling spatial data is how to write queries that deal with such uncertainties. If you want to test whether a point lies on land, you might want to add an acceptable "tolerance" when you test this, so rather than write:

    Point.STIntersects(Land) = 1   <---- Point intersects land

    Point.STDistance(Land) < 100    <---- Point lies within 100m of land

    Having established that your data appears to be correct, does it actually matter to you that a city has been plotted (slightly) in the sea?


    Beginning Spatial with SQL Server http://www.apress.com/book/view/1430218290
    Monday, September 27, 2010 1:56 PM
    Answerer
  • Thanks tanoshimi,

    i understand your reasoning... You're right. So if i change the perspective of the problem.... i need 2 functions.

     

    1) is_in_the_sea (user_lat,user_long,location_id) return bit: check if an user is contained in a determinate circle originated from the coordinates of    the city. control :Point.STDistance(Land) < 300 m for example

    2)if(is_in_the_sea=1) then call: replace_user_in_the sea (wrong_user_lat,wrong_user_long,location_id) return geographic (point in the circle).

    Question: how can i get a random point in the circle of 300m originated by location_coordinates? radium*rand() i can calculate the random distance from the center, but for the latitude and longitude?

     

    thanks tanoshimi

     

    Monday, September 27, 2010 3:21 PM
  • STPointOnSurface (only in geometry). It works even with polygon holes (in your case, the lake), and the point lies always in the surface
    Monday, September 27, 2010 4:06 PM
  • thx windex, is there the equivalent for geographic data? in this case the lake is passed away... :) i have this: geographical point.STBUFFER(300) and i want a random point in his surface.

     

    Massimo

    Monday, September 27, 2010 10:44 PM
  • declare @p geography = geography::Point(0,0,4326);
    declare @buffer float = 300
    
    declare @res geography
    set @res = geography::STPointFromText(
    	geometry::Parse(@p.STBuffer(@buffer).ToString()).STPointOnSurface().ToString()
    	,4326)
    

    Tuesday, September 28, 2010 7:13 AM
  • Hi tanoshimi,

    Could you please tell me how to display "Spatial results" in your reply? It looks very userful.

    Tuesday, September 28, 2010 7:40 AM
  • Hi Vincent, to diplay use

    SELECT @res.STBuffer(300)
    UNION all
    SELECT @p.STBuffer(3000)

    @VinDex

    but the method STPointonSurface return as definition always an arbitrary point, not a random point, and i need a random point :(

    Massimo

     

    Tuesday, September 28, 2010 8:43 AM
  • @Vincent Li - you need to be using SQL Server 2008 Management Studio - then the "Spatial Results" tab will appear alongside the Results and Messages tab whenever you execute a query that returns geography or geometry data.

    Beginning Spatial with SQL Server http://www.apress.com/book/view/1430218290
    Tuesday, September 28, 2010 8:54 AM
    Answerer
  • @Jossy80, @tanoshimi,

    Thanks for your helps. It is very useful!

    Tuesday, September 28, 2010 8:59 AM
  • To get a random point from a geography:

     

    -- GEOGRAPHY
    declare @geo geography = '...'
    
    -- Get Envelope
    declare @envelope geometry = geometry::Parse(@geo.ToString())
    
    -- Get limits
    declare @x1 float = @envelope.STPointN(1).STX
    declare @x2 float = @envelope.STPointN(3).STX
    declare @y1 float = @envelope.STPointN(1).STY
    declare @y2 float = @envelope.STPointN(3).STY
    
    declare @randomPoint geography = 'POINT EMPTY'
    declare @r1 float
    declare @r2 float
    
    -- Look for point
    while (@randomPoint.STIntersects(@geo)<>1)
    BEGIN
    	set @r1 = RAND()
    	set @r2 = RAND()
    
    	set @randomPoint = geography::Point(
    		@r2*(@y2-@y1) + @y1
    		,@r1*(@x2-@x1) + @x1
    		,@geo.STSrid)
    END
    
    select @randomPoint
    

    • Marked as answer by Jossy80 Monday, October 4, 2010 9:57 PM
    Tuesday, September 28, 2010 11:39 AM
  • Shouldn't that be:

    -- Get Envelope
    declare @envelope geometry = geometry::Parse(@geo.ToString()).STEnvelope()
    

    ?

    Also, don't try this method on a geography linestring (unless you've feeling really lucky!)


    Beginning Spatial with SQL Server http://www.apress.com/book/view/1430218290
    Tuesday, September 28, 2010 12:39 PM
    Answerer
  • Yes, cut and paste problem...

     

     

    Tuesday, September 28, 2010 2:06 PM
  • Hi,

    i used this solution to take a random point of a portion of circumference that intersects a map.

    DECLARE @c geography = @city_point.STBuffer(2000).RingN(1)
        DECLARE @num_points INT =@c.STIntersection(@country_map).STNumPoints()
        IF(@num_points>=1)
        BEGIN
            DECLARE @point_seed INT = CAST(@rand*100000000000000 AS BIGINT)%@num_points+1
            SET @point_return = @c.STIntersection(@country_map).STPointN(@point_seed)
        END

    ELSE.....

    and it works!

    I just want to say thank you for the support .

    thanks Massimo

    • Marked as answer by Jossy80 Monday, October 4, 2010 12:59 PM
    Monday, October 4, 2010 12:59 PM