Timezone for a given lat/long

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

Timezone for a given lat/long

suraj birla-2
How to find the timezone for a given lat/long  using postgis?

We have to requirement to fnd the local timezone for millions of records which has  UTC timestamp and lat/long info.

Google provide API but it's single request per call.. Not feasible for milion records.

Any suggestion?

Thanks
Suraj

_______________________________________________
postgis-users mailing list
[hidden email]
https://lists.osgeo.org/mailman/listinfo/postgis-users
Reply | Threaded
Open this post in threaded view
|

Re: Timezone for a given lat/long

Jorge Gustavo Rocha
Hi,

You can import the timezone shapefile [1] in postgis and then use the
st_contains function to return the proper timezone id. Make sure you use
a GIST index. The timezone table has 27742 rows. If you have points at
the sea, maybe this shapefile is not suitable. Try to find a another one.

[1] http://efele.net/maps/tz/world/

If you have trouble with the query, post here your attempt and we will
try to help.

Regards,

Jorge Gustavo


Às 21:23 de 22-03-2017, suraj birla escreveu:

> How to find the timezone for a given lat/long  using postgis?
>
> We have to requirement to fnd the local timezone for millions of records
> which has  UTC timestamp and lat/long info.
>
> Google provide API but it's single request per call.. Not feasible for
> milion records.
>
> Any suggestion?
>
> Thanks
> Suraj
>
>
> _______________________________________________
> postgis-users mailing list
> [hidden email]
> https://lists.osgeo.org/mailman/listinfo/postgis-users
>

J. Gustavo
--
Jorge Gustavo Rocha
Departamento de Informática
Universidade do Minho
4710-057 Braga
Tel: +351 253604480
Fax: +351 253604471
Móvel: +351 910333888
skype: nabocudnosor
_______________________________________________
postgis-users mailing list
[hidden email]
https://lists.osgeo.org/mailman/listinfo/postgis-users
Reply | Threaded
Open this post in threaded view
|

Re: Timezone for a given lat/long

John Abraham-2
In reply to this post by suraj birla-2
I use

select tz.tzid from location
        join tz_world tz on st_within(location."locationPoint", tz.geom) 

with the tz_world shapefile that I'm 99% sure I got from



--
John Abraham

On Mar 22, 2017, at 3:23 PM, suraj birla <[hidden email]> wrote:

How to find the timezone for a given lat/long  using postgis?

We have to requirement to fnd the local timezone for millions of records which has  UTC timestamp and lat/long info.

Google provide API but it's single request per call.. Not feasible for milion records.

Any suggestion?

Thanks
Suraj
_______________________________________________
postgis-users mailing list
[hidden email]
https://lists.osgeo.org/mailman/listinfo/postgis-users


_______________________________________________
postgis-users mailing list
[hidden email]
https://lists.osgeo.org/mailman/listinfo/postgis-users
Reply | Threaded
Open this post in threaded view
|

Re: Timezone for a given lat/long

suraj birla-2
Thanks John and Jorge. It worked.

On Wed, Mar 22, 2017 at 4:21 PM, John Abraham <[hidden email]> wrote:
I use

select tz.tzid from location
        join tz_world tz on st_within(location."locationPoint", tz.geom) 

with the tz_world shapefile that I'm 99% sure I got from



--
John Abraham

On Mar 22, 2017, at 3:23 PM, suraj birla <[hidden email]> wrote:

How to find the timezone for a given lat/long  using postgis?

We have to requirement to fnd the local timezone for millions of records which has  UTC timestamp and lat/long info.

Google provide API but it's single request per call.. Not feasible for milion records.

Any suggestion?

Thanks
Suraj
_______________________________________________
postgis-users mailing list
[hidden email]
https://lists.osgeo.org/mailman/listinfo/postgis-users


_______________________________________________
postgis-users mailing list
[hidden email]
https://lists.osgeo.org/mailman/listinfo/postgis-users


_______________________________________________
postgis-users mailing list
[hidden email]
https://lists.osgeo.org/mailman/listinfo/postgis-users
Reply | Threaded
Open this post in threaded view
|

Re: Timezone for a given lat/long

suraj birla-2
I imported timezone shapefile into postgis to get the timezone for the lat/lon.

We have postgis on Greenplum with 32 segment and  running postgis query to get TZ for 100K records takes around 70-90 secs.

I've index on geom table on both tz_world and source table .

Question : Whether 70-90 second to get TZ from tz_world table is good for 100K records on a 32 segment cluster?

2. Another requirement we have is to show 3 digit timezone code( for eg, PST/PDT, EST/EDT , GMT , etc) for the derived tz_id (America/Los_Angeles)?






On Mon, Mar 27, 2017 at 9:47 PM, suraj birla <[hidden email]> wrote:
Thanks John and Jorge. It worked.

On Wed, Mar 22, 2017 at 4:21 PM, John Abraham <[hidden email]> wrote:
I use

select tz.tzid from location
        join tz_world tz on st_within(location."locationPoint", tz.geom) 

with the tz_world shapefile that I'm 99% sure I got from



--
John Abraham

On Mar 22, 2017, at 3:23 PM, suraj birla <[hidden email]> wrote:

How to find the timezone for a given lat/long  using postgis?

We have to requirement to fnd the local timezone for millions of records which has  UTC timestamp and lat/long info.

Google provide API but it's single request per call.. Not feasible for milion records.

Any suggestion?

Thanks
Suraj
_______________________________________________
postgis-users mailing list
[hidden email]
https://lists.osgeo.org/mailman/listinfo/postgis-users


_______________________________________________
postgis-users mailing list
[hidden email]
https://lists.osgeo.org/mailman/listinfo/postgis-users



_______________________________________________
postgis-users mailing list
[hidden email]
https://lists.osgeo.org/mailman/listinfo/postgis-users