Take the 2-minute tour ×
Geographic Information Systems Stack Exchange is a question and answer site for cartographers, geographers and GIS professionals. It's 100% free, no registration required.

I have a PostGIS-enabled PostgreSQL 9.1 installation with the 10M physical dataset from Natural Earth imported. It's been converted to 900913, and in QGIS I can visualize the data. So the plumbing's all set.

I have a very simple task: I'd like a SQL query to determine if a given point is land or water. Here's a very simple use case: user mouse-downs on a slippy map, the server takes that point and replies "that's land" or "that's not land." I feel that this is surely accomplished with the ne_10m_land layer (with enough accuracy for my purposes).

I'm a newbie to GIS, so while I've tried to do my homework I feel like I must be missing something really obvious to accomplish this...

share|improve this question
    
is your natural earth data vector data, polygons? –  Mapperz Feb 27 '13 at 1:20
    
you don't say how your users will interact with the data (will that be QGIS too?). Depending on your application you might just colour water blue and land green, then inspect the colour of the pixel under the mouse pointer. Also, why can't your users answer this question themselves? –  tomfumb Feb 27 '13 at 2:11
    
@mapperz - the ne_10m_land table that I'm using is Multipolygon. @tomfumb - Sorry, the simple use-case doesn't capture the whole task; suffice it to say that I want the server to make the determination, in order to prevent 'cheating' from the client end. I hope that the GIS data will permit quick & definitive land/no-land determination server-side. –  Chris P. Feb 27 '13 at 6:04
add comment

1 Answer 1

up vote 1 down vote accepted

I am asuming that your slippy map will be a JavaScript Application that runs withing a Browser.

JavaScript cannot directly access your Database. It will require an intermediary Service, that will make queries on your database, and return the result.

Your service could take in the x & y Parameters of the point the user has clicked, and then do the following query:

select (count(*) >0) as isLand from "10m_land" where 
ST_Intersects(ST_SetSRID(ST_MakePoint(:x,:y), 3857), the_geom);

I am assuming that your map, and hence the point that user has clicked, is in Web Mercator Projection (i.e. with wkid=3857). The query will try to find the number of features that intersect with the given point, and if that number is greater than 0, will return true, otherwise return false.

share|improve this answer
    
This SQL comes very close but I suspect something is not quite right; the fault may be my interpretation. When slightly modified as follows to use 900913 to avoid the different SRIDs error: select (count(*) >0) as isLand from "ne_10m_land" where ST_Intersects(ST_SetSRID(ST_MakePoint(0, 0), 900913), the_geom); ...the result is true. This seems peculiar, as point 0/0 is in the ocean off the coast of Africa. –  Chris P. Feb 27 '13 at 5:54
    
I'm not familiar with the data that you are using, but does it also contain a polygon for the sea? And please edit the SRID to 3857 (of your data as well as the SQL). That is the standard SRID. 900913 was just a temporary work around started by Google & OpenLayers. –  Devdatta Tengshe Feb 27 '13 at 6:12
1  
Thanks - that SRID change is good advice. I've discovered through experimentation that your SQL correctly differentiates land/no-land for unconverted 4326 data (in other words, the stock data as delivered from Natural Earth). The SQL fails strictly for the (0,0) position on converted data. I haven't the foggiest why. But a yard away to (1,1) and your SQL succeeds. I think I'll call that adequate. Accepting your answer, thank you very much for your help. –  Chris P. Feb 27 '13 at 7:25
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.