Find the intersection for a latitude and longitude

gistfile1.sql

--First, this is quick hack ported from https://github.com/fruminator/openstreetblock/

--Replace the latitude and longitude accordly on the different places and encode the whole SQL or it will not work


http://vizzuality.cartodb.com/api/v1/sql?q=

select l.name, l.osm_id   from osm_line l   join way_nodes wn   on l.osm_id = wn.way_id   where wn.node_id = (    select n.id   from nodes n   where ARRAY[n.id::integer] && (select w.nodes from osm_line l join osm_ways w on l.osm_id = w.id    where         st_intersects(way         , ST_buffer(ST_SetSRID(ST_MakePoint(-73.999548,40.71954),4326), .002)) order by way <-> ST_SetSRID(ST_MakePoint(-73.999548,40.71954),4326)       limit 1) order by n.geom <-> ST_SetSRID(ST_MakePoint(-73.999548,40.71954),4326)   limit 1) and wn.way_id != (   select osm_id from osm_line l   join osm_ways w on l.osm_id = w.id   where st_intersects(way, ST_buffer(ST_SetSRID(ST_MakePoint(-73.999548,40.71954),4326), .002)   ) LIMIT 1)     and l.name is not null and l.name != ''


--example

http://vizzuality.cartodb.com/api/v1/sql?q=select%20l.name%2C%20l.osm_id%20%20%20from%20osm_line%20l%20%20%20join%20way_nodes%20wn%20%20%20on%20l.osm_id%20%3D%20wn.way_id%20%20%20where%20wn.node_id%20%3D%20(%20%20%20%20select%20n.id%20%20%20from%20nodes%20n%20%20%20where%20ARRAY%5Bn.id%3A%3Ainteger%5D%20%26%26%20(select%20w.nodes%20from%20osm_line%20l%20join%20osm_ways%20w%20on%20l.osm_id%20%3D%20w.id%20%20%20%20where%20%20%20%20%20%20%20%20%20st_intersects(way%20%20%20%20%20%20%20%20%20%2C%20ST_buffer(ST_SetSRID(ST_MakePoint(-73.999548%2C40.71954)%2C4326)%2C%20.002))%20order%20by%20way%20%3C-%3E%20ST_SetSRID(ST_MakePoint(-73.999548%2C40.71954)%2C4326)%20%20%20%20%20%20%20limit%201)%20order%20by%20n.geom%20%3C-%3E%20ST_SetSRID(ST_MakePoint(-73.999548%2C40.71954)%2C4326)%20%20%20limit%201)%20and%20wn.way_id%20!%3D%20(%20%20%20select%20osm_id%20from%20osm_line%20l%20%20%20join%20osm_ways%20w%20on%20l.osm_id%20%3D%20w.id%20%20%20where%20st_intersects(way%2C%20ST_buffer(ST_SetSRID(ST_MakePoint(-73.999548%2C40.71954)%2C4326)%2C%20.002)%20%20%20)%20LIMIT%201)%20%20%20%20%20and%20l.name%20is%20not%20null%20and%20l.name%20!%3D%20''

--- Here is in multiple lines


select l.name, l.osm_id
from osm_line l
join way_nodes wn
on l.osm_id = wn.way_id
where wn.node_id = (
select n.id
from nodes n
where ARRAY[n.id::integer] && (select w.nodes from osm_line l
    join osm_ways w on l.osm_id = w.id
    where 
     st_intersects(way
      , ST_buffer(ST_SetSRID(ST_MakePoint(-73.999548,40.71954),4326), .002)
    )
    order by way <-> ST_SetSRID(ST_MakePoint(-73.999548,40.71954),4326)
    limit 1)
order by n.geom <-> ST_SetSRID(ST_MakePoint(-73.999548,40.71954),4326)
limit 1
)
and wn.way_id != (
select osm_id from osm_line l
join osm_ways w on l.osm_id = w.id
where 
 st_intersects(way
  , ST_buffer(ST_SetSRID(ST_MakePoint(-73.999548,40.71954),4326), .002)
) LIMIT 1)

and l.name is not null and l.name != ''

Uhmmm this is weird. Can I get the URL you are trying? Don't see anything really obvious from this.

…

On 02/02/2012, at 06:51, ***@***.*** wrote: Hi Javier, I've managed to get the integer[] data type into cartodb now using the SQL API so thanks for that. The query is still having problems running and I think I narrowed it down to this part of the statement: ...where ARRAY[n.id::integer] && (select w.nodes from osm_line l join osm_ways w on l.osm_id = w.id where st_intersects(l.the_geom, ST_buffer(ST_SetSRID(ST_MakePoint(-1.911986,52.48534),4326), .002)) order by l.the_geom <-> ST_SetSRID(ST_MakePoint(-1.911986,52.48534),4326) limit 1) It reports an error: argument of WHERE must be type boolean, not type integer[]. Running the same query directly in PostGRES works fine though and I get the correct results. Thanks again, Peter. --- Reply to this email directly or view it on GitHub: https://gist.github.com/1678789

It's working now! Sorry about that, I'm not sure what the problem was - might have been because I was trying to request it unencoded before.

Thanks again for all your help, this is going to be really useful for us.

Peter.