[OT] Venues in the proximity

classic Classic list List threaded Threaded
8 messages Options
Reply | Threaded
Open this post in threaded view
|

[OT] Venues in the proximity

Jolle Carlestam-2
Greetings!

Anyone with suggestions on how to setup a venues DB with a need to be able to identify venues that are close to a given geographical point? Say all venues within 3 kilometers from me.

What’s the best data to store for each venue?
How would the question be articulated that finds them?

HDB
Jolle

#############################################################

This message is sent to you because you are subscribed to
  the mailing list Lasso [hidden email]
Official list archives available at http://www.lassotalk.com
To unsubscribe, E-mail to: <[hidden email]>
Send administrative queries to  <[hidden email]>
Reply | Threaded
Open this post in threaded view
|

Re: [OT] Venues in the proximity

Tim Taplin
If you can provide the longitude and latitude of each location, then the radius lookup of proximity is not that hard.

I posted a couple years ago some code for a lasso tag to do this search, and a mysql procedure that will do the same.

There is one variable in the formula that needs to be changed based on whether your measurement will be kilometers or miles.

I can dig it up if you like.

Tim Taplin

> On Jan 21, 2016, at 1:24 AM, Jolle Carlestam <[hidden email]> wrote:
>
> Greetings!
>
> Anyone with suggestions on how to setup a venues DB with a need to be able to identify venues that are close to a given geographical point? Say all venues within 3 kilometers from me.
>
> What’s the best data to store for each venue?
> How would the question be articulated that finds them?
>
> HDB
> Jolle
>
> #############################################################
>
> This message is sent to you because you are subscribed to
>  the mailing list Lasso [hidden email]
> Official list archives available at http://www.lassotalk.com
> To unsubscribe, E-mail to: <[hidden email]>
> Send administrative queries to  <[hidden email]>


#############################################################

This message is sent to you because you are subscribed to
  the mailing list Lasso [hidden email]
Official list archives available at http://www.lassotalk.com
To unsubscribe, E-mail to: <[hidden email]>
Send administrative queries to  <[hidden email]>
Reply | Threaded
Open this post in threaded view
|

Re: [OT] Venues in the proximity

Marc Vos-3
In reply to this post by Jolle Carlestam-2
Hi Jolle,

A long time ago I made a Lasso page for calculating distances in a straight line.

http://marc.vos.net/howto/postcode/

- -
Marc


> On 21 jan. 2016, at 09:24, Jolle Carlestam <[hidden email]> wrote:
>
> Greetings!
>
> Anyone with suggestions on how to setup a venues DB with a need to be able to identify venues that are close to a given geographical point? Say all venues within 3 kilometers from me.
>
> What’s the best data to store for each venue?
> How would the question be articulated that finds them?
>
> HDB
> Jolle
>
> #############################################################
>
> This message is sent to you because you are subscribed to
>  the mailing list Lasso [hidden email]
> Official list archives available at http://www.lassotalk.com
> To unsubscribe, E-mail to: <[hidden email]>
> Send administrative queries to  <[hidden email]>



#############################################################

This message is sent to you because you are subscribed to
  the mailing list Lasso [hidden email]
Official list archives available at http://www.lassotalk.com
To unsubscribe, E-mail to: <[hidden email]>
Send administrative queries to  <[hidden email]>
Reply | Threaded
Open this post in threaded view
|

Re: [OT] Venues in the proximity

stevepiercy
In reply to this post by Jolle Carlestam-2
See my ancient article.
http://www.stevepiercy.com/articles/zip_distance-calculate-the-distance-between-two-us-zip-codes-with-lasso-8/

The underlying maths are useful, as much as you would disparage
Lasso 8 at every opportunity.

The key is to use latitude and longitude in your data source.

PostgreSQL has some pretty cool geolocation stuff, too.

Also check out Google Maps API for distances.
https://developers.google.com/maps/documentation/distance-matrix/

--steve


On 1/21/16 at 9:24 AM, [hidden email] (Jolle Carlestam) pronounced:

>Greetings!
>
>Anyone with suggestions on how to setup a venues DB with a need
>to be able to identify venues that are close to a given
>geographical point? Say all venues within 3 kilometers from me.
>
>What’s the best data to store for each venue?
>How would the question be articulated that finds them?
>
>HDB
>Jolle
>
>#############################################################
>
>This message is sent to you because you are subscribed to
>the mailing list Lasso [hidden email]
>Official list archives available at http://www.lassotalk.com
>To unsubscribe, E-mail to: <[hidden email]>
>Send administrative queries to  <[hidden email]>

-- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- --
Steve Piercy              Website Builder              Soquel, CA
<[hidden email]>               <http://www.StevePiercy.com/>


#############################################################

This message is sent to you because you are subscribed to
  the mailing list Lasso [hidden email]
Official list archives available at http://www.lassotalk.com
To unsubscribe, E-mail to: <[hidden email]>
Send administrative queries to  <[hidden email]>
Reply | Threaded
Open this post in threaded view
|

Re: [OT] Venues in the proximity

Johan Solve
In reply to this post by Jolle Carlestam-2
MySQL has support for spatial data. I've never worked with it though.

https://dev.mysql.com/doc/refman/5.7/en/spatial-extensions.html
https://dev.mysql.com/doc/refman/5.7/en/spatial-analysis-functions.html

2016-01-21 9:24 GMT+01:00 Jolle Carlestam <[hidden email]>:

> Greetings!
>
> Anyone with suggestions on how to setup a venues DB with a need to be able to identify venues that are close to a given geographical point? Say all venues within 3 kilometers from me.
>
> What’s the best data to store for each venue?
> How would the question be articulated that finds them?
>
> HDB
> Jolle
>
> #############################################################
>
> This message is sent to you because you are subscribed to
>   the mailing list Lasso [hidden email]
> Official list archives available at http://www.lassotalk.com
> To unsubscribe, E-mail to: <[hidden email]>
> Send administrative queries to  <[hidden email]>



--
Mvh
Johan Sölve
____________________________________
Montania System AB
Halmstad   Stockholm
http://www.montania.se

Johan Sölve
Mobil +46 709-51 55 70
[hidden email]

Kristinebergsvägen 17, S-302 41 Halmstad, Sweden
Telefon +46 35-136800 |  Fax +46 35-136801

#############################################################

This message is sent to you because you are subscribed to
  the mailing list Lasso [hidden email]
Official list archives available at http://www.lassotalk.com
To unsubscribe, E-mail to: <[hidden email]>
Send administrative queries to  <[hidden email]>
Reply | Threaded
Open this post in threaded view
|

Re: [OT] Venues in the proximity

Jolle Carlestam-2
Thanks for all insights and answers, Steve, Johan, Tim, Marc and Ke. I now have the knowledge I need to do a Mysql query and get the desired locations.

Excellent!

HDB
Jolle

#############################################################

This message is sent to you because you are subscribed to
  the mailing list Lasso [hidden email]
Official list archives available at http://www.lassotalk.com
To unsubscribe, E-mail to: <[hidden email]>
Send administrative queries to  <[hidden email]>
Reply | Threaded
Open this post in threaded view
|

Re: [OT] Venues in the proximity

Bil Corry-3
FWIW, the way I've always done it is to take the centerpoint (lat/lon),
then calculate a box around it for the distance desired, find those in the
db, then if you strictly need a radius, do a distance calculation between
the centerpoint and each found item.

This provides the info needed for $radius in the SQL query below:

http://www.lassosoft.com/tagswap/detail/lp_math_GPSradius

This is what the SQL query looks like, assuming the zipcode table contains
the lat/lon and it orders closest to furthest:

        SELECT
                store.name,
                store.address,
                etc...
        FROM
                store,
                zipcode
        WHERE
                store.zip = '97333' AND
                store.zip = zipcode.zip AND
                zipcode.lat BETWEEN $radius->lat_low AND $radius->lat_high
                zipcode.lon BETWEEN $radius->lon_low AND $radius->lon_high
        ORDER BY
                SQRT(POW(zipcode.lat - $centerpointLAT,2) + POW(zipcode.lon
- $centerpointLON,2)) ASC


- Bil



On Fri, Jan 22, 2016 at 3:27 PM, Jolle Carlestam <[hidden email]>
wrote:

> Thanks for all insights and answers, Steve, Johan, Tim, Marc and Ke. I now
> have the knowledge I need to do a Mysql query and get the desired locations.
>
> Excellent!
>
> HDB
> Jolle
>
> #############################################################
>
> This message is sent to you because you are subscribed to
>   the mailing list Lasso [hidden email]
> Official list archives available at http://www.lassotalk.com
> To unsubscribe, E-mail to: <[hidden email]>
> Send administrative queries to  <[hidden email]>
>

#############################################################

This message is sent to you because you are subscribed to
  the mailing list Lasso [hidden email]
Official list archives available at http://www.lassotalk.com
To unsubscribe, E-mail to: <[hidden email]>
Send administrative queries to  <[hidden email]>
Reply | Threaded
Open this post in threaded view
|

Re: [OT] Venues in the proximity

Jolle Carlestam-2
25 jan 2016 kl. 11:01 skrev Bil Corry <[hidden email]>:
>
> FWIW

Thanks Bil!

Smart thinking.

HDB
Jolle

#############################################################

This message is sent to you because you are subscribed to
  the mailing list Lasso [hidden email]
Official list archives available at http://www.lassotalk.com
To unsubscribe, E-mail to: <[hidden email]>
Send administrative queries to  <[hidden email]>