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 != ''

We are not finished yet :) When I try it for my apartment location:

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

I get:

"Bergen Street";5681491
"5th Avenue";46865766
"Bergen St";46865767
"5th Avenue";68523765

Both streets twice! Might have to do a distinct on the name or something, going to keep looking at it. By comparison:

http://api.taxono.my:3000/osb?lat=40.68121&lon=-73.97635 =

{
mainStreet: {
name: "Bergen St"
},
inBetween: [
{
name: "5th Ave"
},
{
name: "Flatbush Ave"
}
]
}

Yes. Selecting only the name (not the id) and doing it distinct :)

…

On 25/01/2012, at 20:48, Alastair ***@***.*** wrote: We are not finished yet :) When I try it for my apartment location: http://vizzuality.cartodb.com/api/v1/sql?q=select%20distinct%20l.name%2C%20l.osm_id%20from%20osm_line%20l%20join%20way_nodes%20wn%20on%20l.osm_id%20%3D%20wn.way_id%20where%20wn.node_id%20%3D%20%28%20select%20n.id%20from%20nodes%20n%20where%20ARRAY%5Bn.id%3A%3Ainteger%5D%20%26%26%20%28select%20w.nodes%20from%20osm_line%20l%20%20%20%20%20%20%20join%20osm_ways%20w%20on%20l.osm_id%20%3D%20w.id%20%20%20%20%20where%20%20%20%20%20%20%20st_intersects%28way%20%20%20%20%20%20%20%2C%20ST_buffer%28ST_SetSRID%28ST_MakePoint%28-73.97635%2C%2040.68121%29%2C4326%29%2C%20.002%29%20%20%20%20%20%29%20%20%20%20%20order%20by%20way%20%3C-%3E%20ST_SetSRID%28ST_MakePoint%28-73.97635%2C%2040.68121%29%2C4326%29%20%20%20%20%20limit%201%29%20order%20by%20n.geom%20%3C-%3E%20ST_SetSRID%28ST_MakePoint%28-73.97635%2C%2040.68121%29%2C4326%29%20limit%201%20%29%20and%20wn.way_id%20%21%3D%20%28%20select%20osm_id%20from%20osm_line%20l%20%20join%20osm_ways%20w%20on%20l.osm_id%20%3D%20w.id%20%20where%20%20%20st_in tersects%28way%20%20%20%20%2C%20ST_buffer%28ST_SetSRID%28ST_MakePoint%28-73.97635%2C%2040.68121%29%2C4326%29%2C%20.002%29%20%29%20LIMIT%201%29%20%20and%20l.name%20is%20not%20null%20and%20l.name%20%21%3D%20%27%27 I get: "Bergen Street";5681491 "5th Avenue";46865766 "Bergen St";46865767 "5th Avenue";68523765 Both streets twice! Might have to do a distinct on the name or something, going to keep looking at it. --- Reply to this email directly or view it on GitHub: https://gist.github.com/1678789

Hello,

I've found this really useful, thanks! However in trying to host it on my own cartodb instance I've come across a few problems. The latest one I'm not sure how to get around. The column nodes in the osm_ways table is an array of integer but when I've tried to import it to cartodb it puts it as a string and can't seem to handle integer arrays. So the query fails with this error:

{"error":["operator does not exist: double precision = character varying"]}

How is the column stored in the vizzuality instance?

Thanks,

Peter.

Well, You cached me here :) We dont support arrays on current CartoDB servers now. But for dedicated instances, like ours, we get direct access to PostgreSQL and we can then use them because internally they are supported. So yeah, this is not really possible with a free plan right now on CartoDB. But I am not sure if you mean that you have a dedicated instance? Do you? And you are loading osm_ways using direct loading?

…

On Jan 31, 2012, at 7:34 AM, peterbaldwin wrote: Hello, I've found this really useful, thanks! However in trying to host it on my own cartodb instance I've come across a few problems. The latest one I'm not sure how to get around. The column nodes in the osm_ways table is an array of integer but when I've tried to import it to cartodb it puts it as a string and can't seem to handle integer arrays. So the query fails with this error: {"error":["operator does not exist: double precision = character varying"]} How is the column stored in the vizzuality instance? Thanks, Peter. --- Reply to this email directly or view it on GitHub: https://gist.github.com/1678789

Hi Javier, Thank you for for your reply. We are just using a free plan at the moment so that would explain why I can't get it to work. I imported the osm_ways table as a CSV as well as way_nodes. The osm_line and nodes tables were imported as shape files. It's not a big deal as we can do the same calls directly in the PostGIS database that we have. Thanks again, Peter.

Hi Peter, Actually there is ways to do this... you can create a column using the SQL API and then populated with the content of the others... But while we make this a little bit nastier I actually recommend you to do it on your own PostGIS DB Cheers.

…

On Feb 1, 2012, at 4:26 AM, peterbaldwin wrote: Hi Javier, Thank you for for your reply. We are just using a free plan at the moment so that would explain why I can't get it to work. I imported the osm_ways table as a CSV as well as way_nodes. The osm_line and nodes tables were imported as shape files. It's not a big deal as we can do the same calls directly in the PostGIS database that we have. Thanks again, Peter. Application Support Analyst Middleware Technologies Team ## Service Birmingham A: B1 Building, Summer Hill Road, Birmingham, B1 3RB E: ***@***.*** T: 0121 303 8776 ## M: 07833 059297 From: Javier de la Torre ***@***.*** To: peterbaldwin ***@***.*** Date: 31/01/2012 16:28 Subject: Re: gist gist: 1678789 Well, You cached me here :) We dont support arrays on current CartoDB servers now. But for dedicated instances, like ours, we get direct access to PostgreSQL and we can then use them because internally they are supported. So yeah, this is not really possible with a free plan right now on CartoDB. But I am not sure if you mean that you have a dedicated instance? Do you? And you are loading osm_ways using direct loading? On Jan 31, 2012, at 7:34 AM, peterbaldwin wrote: > Hello, > > I've found this really useful, thanks! However in trying to host it on my > own cartodb instance I've come across a few problems. The latest one I'm > not sure how to get around. The column nodes in the osm_ways table is an > array of integer but when I've tried to import it to cartodb it puts it as > a string and can't seem to handle integer arrays. So the query fails with > this error: > > {"error":["operator does not exist: double precision = character > varying"]} > > How is the column stored in the vizzuality instance? > > Thanks, > > Peter. > --- > > Reply to this email directly or view it on GitHub: > https://gist.github.com/1678789 --- Reply to this email directly or view it on GitHub: https://gist.github.com/1678789 --- The information contained within this e-mail (and any attachment) sent by Service Birmingham Ltd. is confidential and may be legally privileged. It is intended only for the named recipient or entity to whom it is addressed. If you are not the intended recipient, please notify the sender and delete the e-mail immediately. Unauthorised access, use, disclosure, storage or copying is not permitted and may be unlawful. Any e-mail including its content may be monitored and used by Service Birmingham Ltd. for reasons of security and for monitoring internal compliance with Security Policy. E-mail blocking software will also be used. Any views or opinions expressed are solely those of the originator and do not necessarily represent those of Service Birmingham Ltd. Although Service Birmingham Ltd. has made every reasonable effort to ensure that this message and any attachments contain no viruses and have not been intercepted or amended, it can not make any assurances to this effect. Service Birmingham Ltd., Registered in England & Wales under Company No. 05660977 Registered Office: Units 2&3, B1, 50 Summer Hill Road, Birmingham, B1 3BZ. --- --- Reply to this email directly or view it on GitHub: https://gist.github.com/1678789

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.

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.