Re: [postgis-users] Generating new random points throughout an update

[Brent Wood via postgis-users]

 Gotcha...
I'm glad it didn't really make sense to you either. That is sort of what I 
thought might be happening.

Nice to know I'm not a totally lost cause!!!

That works perfectly,

Thanks heaps!!


On Sunday, November 19, 2023 at 11:09:34 AM GMT+13, Regina Obe 
 wrote:  
 
 
I’m guessing the reason why your example doesn’t work is because the planner is 
doing some serious short-circuiting cause it sees two subselects that are using 
no variables from the updated table. 

I suspect this is a bug or some intentional stuff that makes no sense to me.  
Cause I’m pretty sure I’ve done something like  UPDATE sometable SET geom = 
ST_GeneratePoints(somestaticgeom,1);

And gotten different answers.  So I suspect it’s the subselect throwing it off 
or it is intentionally treating like a constant because that subselect doesn’t 
involve the table being updated.

  

So the trick I see is to incorporate some value from your events table into 
your routine.

  

Try computing first and then updating like so the below gives me different 
answers for each row.

  

WITH a AS (

SELECT e.id, ST_Makeline(

    ST_GeometryN(

  ST_GeneratePoints(

    ST_Buffer(

  ST_POINTN(s.std_track,1),

  0.01),

  1),

    1), ST_GeometryN(

  ST_GeneratePoints(

    ST_Buffer(

  ST_POINTN(s.std_track,2),

  0.01),

    1),

  1)

    ) AS geom

FROM events AS e, std_tow AS s

)

UPDATE events

set jittered = a.geom

FROM a

WHERE a.id = events.id;

select ST_AsText(jittered) from events;

  

the other way to do it, making the planner realize that just cause the code is 
exactly the same and doesn’t involve the table being updated, doesn’t mean you 
want all your values to be the same, is to incorporate your event id in your 
randomize like so

  

update events

set jittered = ST_Makeline(

    (select ST_GeometryN(

  ST_GeneratePoints(

    ST_Buffer(

  ST_POINTN(std_track,1),

  0.01),

  1,

  (random()*1000)::int + events.id),

   1)

 from std_tow),

    (select ST_GeometryN(

  ST_GeneratePoints(

    ST_Buffer(

  ST_POINTN(std_track,2),

  0.01),

    1,

    (random()*1000)::int + events.id),

    1)

 from std_tow));

select ST_AsText(jittered) from events;

  

  

  

  

From: Brent Wood  
Sent: Saturday, November 18, 2023 4:35 PM
To: Regina Obe ; PostGIS Users Discussion 

Subject: Re: [postgis-users] Generating new random points throughout an update

  

Thanks for your time & advice Regina, I appreciate it.

  

  

I still can't get this to work as I think it should, so have included actual 
SQL's to show what I'm doing, using ST_GeneratePoints() this time...

  

I create a db & add the postgis extension

  

Then create the two tables to test, inserting 3 empty geometries in one & a 
simple linestring in the other:

  

create table events (id        integer,

                     jittered  geometry(LINESTRING,4326));

  

insert into events (id) values (1);

insert into events (id) values (2);

insert into events (id) values (3);

  

create table std_tow (id    integer,

                      std_track geometry(LINESTRING,4326));

  

insert into std_tow values (1, ST_SetSRID(

                                 ST_MakeLine(

    ST_MakePoint(176,-47),

    ST_MakePoint(177,-48)

                                 ),

         4326)); 



  

I want to update the empty linestrings in one table (events) with slightly 
randomised versions of the linestring in the other (std_tow).

ST_GeneratePoints() supposedly generates random points (in a polygon created by 
buffering the vertices in the standard linestring) without a seed, so I run it 
with no seed & view the results:

  

update events
set jittered = ST_Makeline(
    (select ST_GeometryN(
  ST_GeneratePoints(
    ST_Buffer(
  ST_POINTN(std_track,1),
  0.01),
  1),
        1)
 from std_tow),
    (select ST_GeometryN(
  ST_GeneratePoints(
    ST_Buffer(
  ST_POINTN(std_track,2),
  0.01),
    1),
          1)

 from std_tow));

select ST_AsText(jittered) from events;

  

LINESTRING(175.99658281229873 -46.99893493622685,177.0081812507064 
-47.99873318845546)
LINESTRING(175.99658281229873 -46.99893493622685,177.0081812507064 
-47.99873318845546)
LINESTRING(175.99658281229873 -46.99893493622685,1

Re: [postgis-users] Generating new random points throughout an update

[Brent Wood via postgis-users]

 Thanks for your time & advice Regina, I appreciate it.

I still can't get this to work as I think it should, so have included actual 
SQL's to show what I'm doing, using ST_GeneratePoints() this time...

I create a db & add the postgis extension
Then create the two tables to test, inserting 3 empty geometries in one & a 
simple linestring in the other:
create table events (id        integer,                     jittered  
geometry(LINESTRING,4326));
insert into events (id) values (1);insert into events (id) values (2);insert 
into events (id) values (3);
create table std_tow (id    integer,                      std_track 
geometry(LINESTRING,4326));
insert into std_tow values (1, ST_SetSRID(                                 
ST_MakeLine(    ST_MakePoint(176,-47),  
      ST_MakePoint(177,-48)                                 
),         4326)); 

I want to update the empty linestrings in one table (events) with slightly 
randomised versions of the linestring in the other 
(std_tow).ST_GeneratePoints() supposedly generates random points (in a polygon 
created by buffering the vertices in the standard linestring) without a seed, 
so I run it with no seed & view the results:
update events
set jittered = ST_Makeline(
    (select ST_GeometryN(
  ST_GeneratePoints(
    ST_Buffer(
  ST_POINTN(std_track,1),
  0.01),
  1),
        1)
 from std_tow),
    (select ST_GeometryN(
  ST_GeneratePoints(
    ST_Buffer(
  ST_POINTN(std_track,2),
  0.01),
    1),
          1)
 from std_tow));select ST_AsText(jittered) from events;
LINESTRING(175.99658281229873 -46.99893493622685,177.0081812507064 
-47.99873318845546)
LINESTRING(175.99658281229873 -46.99893493622685,177.0081812507064 
-47.99873318845546)
LINESTRING(175.99658281229873 -46.99893493622685,177.0081812507064 
-47.99873318845546)
(3 rows)

I get three identical linestrings. 
I figured I'd use a different integer random seed (between 0 and 1000) in 
ST_GeneratePoints() to force a different result each time:
update events
set jittered = ST_Makeline(
    (select ST_GeometryN(
  ST_GeneratePoints(
    ST_Buffer(
  ST_POINTN(std_track,1),
  0.01),
  1,
          (random()*1000)::int),
           1)
 from std_tow),
    (select ST_GeometryN(
  ST_GeneratePoints(
    ST_Buffer(
  ST_POINTN(std_track,2),
  0.01),
    1,
            (random()*1000)::int),
            1)
 from std_tow));
select ST_AsText(jittered) from events;
 LINESTRING(175.9943248467802 -46.996045972449906,176.9919097521138 
-48.00102135929174)
 LINESTRING(175.9943248467802 -46.996045972449906,176.9919097521138 
-48.00102135929174)
 LINESTRING(175.9943248467802 -46.996045972449906,176.9919097521138 
-48.00102135929174)
(3 rows)

I get different vertices from the first attempt, but all 3 records are still 
the same values - despite supposedly having a different seed.
I figure the Postgres query optimiser must be reusing the result from the 
subqueries rather than recalculating it each time, but am not sure, and the 
optimiser cannot be turned off.

What am I doing wrong??? (or how can I do it right!!)


Much appreciated,
   Brent




On Sunday, November 19, 2023 at 06:44:16 AM GMT+13, Regina Obe 
 wrote:  
 
 
Well when I run random()  I do get a different answer for each run so random 
behaves as I would expect.  I didn’t look that closely at your query with 
random.

  

e.g.

  

SELECT random()

FROM generate_series(1,100);

  

Even if within the same row, the random numbers are different:

  

SELECT random(), random()

FROM generate_series(1,10);

  

If you were doing random()::integer as input into ST_GeneratePoints, I thought 
maybe that was a typo on your end.  Then your random number would only be 0 or 
1, which is not that random.

  

So if you really were doing ST_GeneratePoints(geom, random()::integer) then 
that would explain why you got much less than random results with 
ST_GeneratePoints.

  

  

From: Brent Wood  
Sent: Saturday, November 18, 2023 1:29 AM
To: Regina Obe ; PostGIS Users Discussion 

Subject: Re: [postgis-users] Generating new random points throughout an update

  

Hi Regina,

  

The seed was an int generated from random(), so I'd expected to generate a 
different result every time. This didn't happen.

  

Do I understand that if I omit the seed, I'll get a different point each time 
by default?

  

  

Thanks,

  

   Brent 

  

On Saturday, November 18, 2023 at 06:01:37 PM GMT+13, Regina Obe 
 wrote: 

  

  

If you want the answer dif

Re: [postgis-users] Generating new random points throughout an update

[Brent Wood via postgis-users]

 Hi Regina,
The seed was an int generated from random(), so I'd expected to generate a 
different result every time. This didn't happen.
Do I understand that if I omit the seed, I'll get a different point each time 
by default?

Thanks,
   Brent 

On Saturday, November 18, 2023 at 06:01:37 PM GMT+13, Regina Obe 
 wrote:  
 
 
If you want the answer different each time, you don’t want to feed a seed to 
ST_GeneratePoints.  

The seed argument was added because some people wanted to generate the same 
answer for each run.

  

https://postgis.net/docs/ST_GeneratePoints.html  (note the sentence: The 
optional seed is used to regenerate a deterministic sequence of points, and 
must be greater than zero.)

  

  

From: postgis-users  On Behalf Of Brent 
Wood via postgis-users
Sent: Friday, November 17, 2023 11:53 PM
To: PostGIS Users Discussion 
Cc: Brent Wood 
Subject: [postgis-users] Generating new random points throughout an update

  

Hopefully someone can help with a problem I'm having.

  

I have a table with simple linestrings that I need to create a randomly 
modified version of.

  

The linestrings represent vessel tracks. I can identify a set of "similar" 
tracks & create a single "average" linestring that is somewhat representative.

  

Many of the records don't have a linestring, but for statistical purposes I 
need to assign a linestring to each - by creating a jittered version of the 
average linestring so they are not all identical.

  

The simplest approach I have tried is to use an update with ST_Project() given 
a random() distance & random() direction applied to each vertex in the average 
line.

  

I use the first two vertices with ST_Makeline(), then append a vertex for the 
third point, as in the SQL below. 

  

My problem is that every new line is identical. From some Googled hints, I 
figure the optimiser has decided to run random() once & re-use the value 
instead of running the function for every iteration (but I could be wrong!).

  

Any suggestions as to how I can force a different random result for each record 
that is updated?

I also tried using ST_GeneratePoints() in a buffer around each point, but need 
to use something like (random()::int as the seed, and this seems to do exactly 
the same - valid linestrings are generated, but they are identical, so I'm 
assuming the seed is not being recalculated for each record.

  

  

update events
set jittered = ST_MakeLine(
   (select ST_Project(
 ST_POINTN(std_track,1),
 (random()*5000),
 radians(random()*360))::geometry
   from std_tow),
                  (select ST_Project(
 ST_PointN(std_track,2),
 (random()*5000),
   radians(random()*360))::geometry
   from std_tow)
  );

  

  

Thanks,

  

  Brent Wood

  
  ___
postgis-users mailing list
postgis-users@lists.osgeo.org
https://lists.osgeo.org/mailman/listinfo/postgis-users

[postgis-users] Generating new random points throughout an update

[Brent Wood via postgis-users]

Hopefully someone can help with a problem I'm having.
I have a table with simple linestrings that I need to create a randomly 
modified version of.
The linestrings represent vessel tracks. I can identify a set of "similar" 
tracks & create a single "average" linestring that is somewhat representative.
Many of the records don't have a linestring, but for statistical purposes I 
need to assign a linestring to each - by creating a jittered version of the 
average linestring so they are not all identical.
The simplest approach I have tried is to use an update with ST_Project() given 
a random() distance & random() direction applied to each vertex in the average 
line.
I use the first two vertices with ST_Makeline(), then append a vertex for the 
third point, as in the SQL below. 

My problem is that every new line is identical. From some Googled hints, I 
figure the optimiser has decided to run random() once & re-use the value 
instead of running the function for every iteration (but I could be wrong!).
Any suggestions as to how I can force a different random result for each record 
that is updated?I also tried using ST_GeneratePoints() in a buffer around each 
point, but need to use something like (random()::int as the seed, and this 
seems to do exactly the same - valid linestrings are generated, but they are 
identical, so I'm assuming the seed is not being recalculated for each record.


update events
set jittered = ST_MakeLine(
   (select ST_Project(
 ST_POINTN(std_track,1),
 (random()*5000),
 radians(random()*360))::geometry
   from std_tow),
                  (select ST_Project(
 ST_PointN(std_track,2),
 (random()*5000),
   radians(random()*360))::geometry
   from std_tow)
      );

Thanks,
  Brent Wood

___
postgis-users mailing list
postgis-users@lists.osgeo.org
https://lists.osgeo.org/mailman/listinfo/postgis-users

Re: [postgis-users] Failing ST_Transform with Ross Ice Shelf polygon

[Brent Wood via postgis-users]

Hi guys,

What happens if the dataset is converted to 0-360 with ST_ShiftLongitude() then 
back again to +-180?

I'd expect that to come up with the +-180 standardised on one or the other, 
rather than having a dataset with both.

It is a shame that more recent versions of proj don't support 0-360 longitudes, 
as that would fix the issue in the Ross Sea (but only by moving it 180deg...)


Brent Wood

Principal Technician, Fisheries
NIWA
DDI:  +64 (4) 3860529

From: postgis-users  on behalf of Paul 
Ramsey via postgis-users 
Sent: Wednesday, November 8, 2023 08:39
To: Marco Boeringa 
Cc: Paul Ramsey ; PostGIS Users Discussion 

Subject: Re: [postgis-users] Failing ST_Transform with Ross Ice Shelf polygon

Hm, the current logic of the nudge would probably still not help your case, 
since your problem is both the coordinate being slightly out of bounds and also 
the change in sign. The nudge would move -180.04 to -180, but it 
wouldn’t flip the sign.

On Nov 7, 2023, at 11:36 AM, Marco Boeringa  wrote:


Sounds interesting. I think many users of PostGIS would be really glad to see 
something like this implemented if it could reasonably be done. Haven't tried 
the double cast via geography yet, but seems fun thing to check and see the 
result.

Op 7-11-2023 om 20:31 schreef Paul Ramsey:
All that said…

It would be possible to “fix” this, but it’s a scary black box.
We already nudge geodetics back into place when casting from geometry to 
geography (interesting workaround, take your reprojected result and do a 
::geography::geometry on it)

https://github.com/postgis/postgis/blob/42f04a29effdd9e8280c7aba17420ba306fc73f4/liblwgeom/lwgeodetic.c#L3351

For systems that we know are geodetics (and with modern proj we generally know 
that) we could apply the nudge to the outputs. It would make things slower 
(more logic) but it would only change those cases where the coordinates are in 
fact out of bounds by a very small amount.

P.

On Nov 7, 2023, at 11:22 AM, Paul Ramsey 
<mailto:pram...@cleverelephant.ca> wrote:

Nope.

It can be quite reasonably argued that the answer is correct, and the problem 
is treating EPSG:4326 (a geodetic coordinate system with angular units) as if 
it was a planar system with cartesian units (spoiler: it is not that). In 
angular units, -180.04 is ridiculously close to 180.0. You aren’t 
complaining about the other coordinates, like where 175.123456789 is coming 
through as 175.123456788. Why not? It’s the same error! :)

I don’t know what it is about the math going through that fun CRS that is 
causing roundoff or even if it’s particularly large (I don’t think it is), but 
it is not at all unique to that system. You can generate data that is 
progressively offset from the original data doing nothing more exotic than 
going back and forth from WGS83 to UTM over and over and over.

ATB,

P

On Nov 7, 2023, at 11:16 AM, Marco Boeringa 
<mailto:ma...@boeringa.demon.nl> wrote:


Thanks Paul,

But is there a more definitive solution in PostGIS / PROJ on the horizon in 
terms of future development? No one expects a perfectly valid geometry that 
just happens to hit the projection boundary of WGS1984 to come out garbled by 
doing a transform and back-transform to the original CRS. I realize there may 
be technical challenges here, but this will undoubtedly keep coming up many 
times in the future, and likely has in the past, by other confused non-expert 
users of PostGIS if nothing changes. It is really counter-intuitive to need to 
use stuff like ST_SnapToGrid, ST_ReducePrecision or ST_WrapX to "fix" something 
that goes right for 99.999% of all other data. It also makes any needed code 
more convoluted.

Yes, well, I know, storing data in WGS 1984 geometry may not be best practice 
with this kind of globe spanning data, but it works for most cases and I 
already cast to geography a lot to do stuff where geography is really needed.

Marco

Op 7-11-2023 om 19:02 schreef Paul Ramsey:


On Nov 6, 2023, at 3:39 PM, Paul Ramsey 
<mailto:pram...@cleverelephant.ca> wrote:



On Nov 6, 2023, at 3:33 PM, Marco Boeringa 
<mailto:ma...@boeringa.demon.nl> wrote:


Well, yes indeed that is what is happening, 180 came out of the reprojection 
steps as -180. Full output geometry below. Is there any way to prevent this 
behavior?

Marco

Not really… Either snap to grid or reduce precision

https://postgis.net/docs/ST_ReducePrecision.html
https://postgis.net/docs/ST_SnapToGrid.html

will get you back onto the dividing line (note that it is at 
-180.14), but that won’t help in flipping -180 to 180. For your 
particular case, applying

https://postgis.net/docs/ST_ShiftLongitude.html

will fix it, I think, though not in generality

I think using

https://postgis.net/docs/ST_WrapX.html

would allow a more general purpose solution. At least one you have more control 
over.

P


P




[https://www.niwa.co.nz/static/niw

Re: [postgis-users] PGAdmin4 - not getting swamped by geometry columns in data view?

[Brent Wood via postgis-users]

Not really an answer, but might help.

I use DBeaver (Community Edition) for most of my GUI based PostGIS access. Not 
as much admin capability as PGAdmin, but much better Postgis support.

It understands Postgis, automatically displays WKT for geometry columns & has a 
simple mapping tool built in to view the Postgis data graphically.


Cheers

Brent Wood

Principal Technician, Fisheries
NIWA
DDI:  +64 (4) 3860529

From: postgis-users  on behalf of Gary 
Turner via postgis-users 
Sent: Wednesday, October 18, 2023 18:50
To: postgis-users@lists.osgeo.org 
Cc: Gary Turner 
Subject: [postgis-users] PGAdmin4 - not getting swamped by geometry columns in 
data view?

Apologies in advance if this is a silly question, or this is an
inappropriate place to ask.

I've recently switched from pgadmin3 to 4 on a new windows machine, and
I occasionally use it to look at metadata. The tables I use
unfortunately tend to have the geometry columns at the left of the table.

In V3, the geometry was width limited. In 4 it's not, and I can't even
scroll past the geometry column to find the data that's to the right of it.

Is there a way to limit the width of the geometry column? Or even hide it?

___
postgis-users mailing list
postgis-users@lists.osgeo.org
https://aus01.safelinks.protection.outlook.com/?url=https%3A%2F%2Flists.osgeo.org%2Fmailman%2Flistinfo%2Fpostgis-users=05%7C01%7Cbrent.wood%40niwa.co.nz%7C921f61ea39e94e8981b108dbcf9f1ea9%7C41caed736a0c468aba499ff6aafd1c77%7C0%7C0%7C638332054555773948%7CUnknown%7CTWFpbGZsb3d8eyJWIjoiMC4wLjAwMDAiLCJQIjoiV2luMzIiLCJBTiI6Ik1haWwiLCJXVCI6Mn0%3D%7C3000%7C%7C%7C=SzL%2FXIj2ib9spDSvqAhOuUXGu7Qd1r7CAJJvyBYPyVo%3D=0<https://lists.osgeo.org/mailman/listinfo/postgis-users>
[https://www.niwa.co.nz/static/niwa-2018-horizontal-180.png] 
<https://www.niwa.co.nz/>
Brent Wood
Principal Technician - GIS and Spatial Data Management
Programme Leader - Environmental Information Delivery
+64-4-386-0529

National Institute of Water & Atmospheric Research Ltd (NIWA)
301 Evans Bay Parade Hataitai Wellington New Zealand
Connect with NIWA: niwa.co.nz<https://www.niwa.co.nz/> 
Facebook<https://www.facebook.com/nzniwa> 
LinkedIn<https://www.linkedin.com/company/niwa> 
Twitter<https://twitter.com/niwa_nz> 
Instagram<https://www.instagram.com/niwa_science> 
YouTube<https://www.youtube.com/channel/UCJ-j3MLMg1H59Ak2UaNLL3A>
To ensure compliance with legal requirements and to maintain cyber security 
standards, NIWA's IT systems are subject to ongoing monitoring, activity 
logging and auditing. This monitoring and auditing service may be provided by 
third parties. Such third parties can access information transmitted to, 
processed by and stored on NIWA's IT systems.
Note: This email is intended solely for the use of the addressee and may 
contain information that is confidential or subject to legal professional 
privilege. If you receive this email in error please immediately notify the 
sender and delete the email.
___
postgis-users mailing list
postgis-users@lists.osgeo.org
https://lists.osgeo.org/mailman/listinfo/postgis-users

Re: [postgis-users] Free Online or Cloud PostGreSQL and PostGIS

[Brent Wood]

 I have a system at home I could set up an account for you to play with, no 
guarantees, etc. if that meets your immediate needs. 

If you wanted something with some assurances of server/data security I think it 
likely you'll need to pay for the privilege.
There is a local (in New Zealand) provider that offers a nice service with good 
support (in my experience anyway). I don't know how the pricing compares with 
others:https://catalystcloud.nz/about/news/catalyst-clouds-kiwi-first-managed-database-service-now-with-postgresql-support/https://catalystcloud.nz/services/paas/managed-database/https://docs.catalystcloud.nz/
At about $NZ25 per month for a minimal system, that could be a cheap way to 
start, but you would need to scale up to support multiple users with 
significant data volumes.

This article may be of 
interest:https://www.prisma.io/dataguide/postgresql/5-ways-to-host-postgresql
I would also suggest that if you just want to try it out, any cheap x64 
computer with 1Gb of memory or so will happily run a Linux setup (very easy to 
build) with Postgres/Postgis installed. The deployment process typically takes 
me about 20 minutes, I'm happy to help you through that if you want. 


Cheers,
Brent Wood






On Sunday, April 2, 2023 at 08:39:07 AM GMT+12, Alexandre Neto 
 wrote:  
 
 https://www.crunchydata.com/developers/playground, maybe?
A sexta, 31/03/2023, 19:59, Tahir Tamba  escreveu:

Hi, Is there a free PostgreSQL or PostGIS environment online or in the cloud 
allowing beginners to access the PostgreSQL and PostGIS server for learning 
purposes with the possibility of storing our own dataset?

Regards,

-- 

Tahir Tamba

6632 Avenue de Gaspé

Montréal (Québec) H2S 2Y2

Courriel: tahir.ta...@gmail.com






AVERTISSEMENT : Ce courriel et les pièces qui y sont jointes sont destinés 
exclusivement au(x) destinataire(s) mentionné(s) ci-dessus et peuvent contenir 
de l’information privilégiée ou confidentielle. Si vous avez reçu ce courriel 
par erreur, ou s’il ne vous est pas destiné, veuillez le mentionner 
immédiatement à l’expéditeur et effacer ce courriel ainsi que les pièces 
jointes, le cas échéant. La copie ou la redistribution non autorisée de ce 
courriel peut être illégale. Le contenu de ce courriel ne peut être interprété 
qu’en conformité avec les lois et règlements qui régissent les pouvoirs des 
diverses instances décisionnelles compétentes de la Ville de Montréal.






___
postgis-users mailing list
postgis-users@lists.osgeo.org
https://lists.osgeo.org/mailman/listinfo/postgis-users

___
postgis-users mailing list
postgis-users@lists.osgeo.org
https://lists.osgeo.org/mailman/listinfo/postgis-users
  ___
postgis-users mailing list
postgis-users@lists.osgeo.org
https://lists.osgeo.org/mailman/listinfo/postgis-users

[postgis-users] Help needed with ogr_fdw to access mysql

[Brent Wood]

Hi,
I have a VRT file that allows a non-spatial MySQL database to be accessed 
directly with QGIS. This works very well.
I'm now trying to use this as the basis to create an FDW in a Postgres db that 
maps the same data extract as a local table. 

I can't find a set of docs or example that I can follow to get this working, 
hence this "Please help!!" email.
The XML for the VRT is:

  
  
MYSQL:x,user=x,password=xhost=x,port=
 
  select catalognumber, latitude1 AS y, case when longitude1 < 0 then 
360+longitude1 else longitude1 end AS x,
localityName as StationID, startDate, latitude1, longitude1, latitude2, 
longitude2, 
maxElevation, minElevation, taxon.fullname as TaxonName, prefT.fullname as 
PreferredName
from collectionobject
INNER JOIN collectingevent ON collectionobject.collectingeventid = 
collectingevent.collectingeventid
INNER JOIN locality on collectingevent.localityid = locality.localityid
LEFT JOIN determination on collectionobject.collectionobjectid = 
determination.collectionobjectid
LEFT JOIN taxon on determination.taxonid = taxon.taxonid
LEFT JOIN taxon prefT on determination.preferredtaxonid = prefT.taxonid
WHERE Latitude1 is not null and longitude1 is not null and 
determination.iscurrent = 1 and catalognumber is not null
ORDER BY Catalognumber 
  wkbPoint
   
  EPSG:4326


I need to convert this to the SQL's to create the same query using fdw in a 
Postgis db. 

I figure I can use the VRT file as the connection directly in ogr_fdw, or 
create a mysql_fdw SQL to achieve the desired result. I'm happy with either, 
any advise appreciated.

If necessary I can retrieve just the coords & use a view to create the postgis 
geometries in the Postgres db, as the source db is non-spatial (just numeric x 
& y coords (decimal degrees).
I have postgres FDW tables working fine, it is the "create server ..." and 
"import ..." SQL statements to connect to a mysql db or OGR VRT file that I'm 
having trouble with.


Thanks,
Brent Wood
___
postgis-users mailing list
postgis-users@lists.osgeo.org
https://lists.osgeo.org/mailman/listinfo/postgis-users

Re: [postgis-users] Lines crossing longitude 180 -180

[Brent Wood]

 Yep, 

Either use a Geography datatype as Neil describes here, or create the line with 
longitudes in the 0-360 space instead of +-180... so 170W = 190 instead of -170.
You can use the ST_ShiftLongitude() function to switch between 0-360 & +-180 
longitude spaces (EPSG supports both for 4326), but proj only supports +-180 
for reprojection.

Brent Wood

On Wednesday, September 25, 2019, 7:36:46 AM GMT+12, Neil Freeman 
 wrote:  
 
 Paul-
You can use a Geography type to automatically handle dateline crossing. This is 
a good resource on the differences between Geography and Geometry types: 
https://postgis.net/workshops/postgis-intro/geography.html
Generating a line from your sample data would look something like this 
(decimals clipped for readability):
SELECT ST_Geographyfromtext('LINESTRING(178.9 -37.5,-179.9 -38.4,-179.7 
-38.4)');
Converting the data from minutes and seconds to decimals could be done in SQL 
or elsewhere.
While PostGIS will correctly handle calculations with the geography, desktop 
GIS programs might have display glitches depending on the map projection. In 
QGIS I was able to correctly view your sample line when using a southern polar 
projection, but not when using a standard mercator projection.
-Neil

From: Subject: [postgis-users] Lines crossing longitude 180 
-180
Date: September 24, 2019 at 3:18:22 AM EDT
To: 


Hi,I’m creating a table with LineStrings in SRID 4326 from lines with 
coordinates like 
this:S37323578E178560795S38161687W179595994S3827W17944S38414463E179595994S39394800E178553000S40224800E178060600S41504422E176164722S42513000E17503S43372622E174062536S45135100E172134000S4809E16816S4555E16518S42254200E169201800S4158E169501800S41253091E170232389As
 you can see the crosses 180/-180 longitude several times which results in long 
line segments all over the map from east to west.Is there anyone who have 
solved this problem in a fairly easy way?I appreciate all help I can get.  Kind 
regards,Paul Malm 

___
postgis-users mailing list
postgis-users@lists.osgeo.org
https://lists.osgeo.org/mailman/listinfo/postgis-users  ___
postgis-users mailing list
postgis-users@lists.osgeo.org
https://lists.osgeo.org/mailman/listinfo/postgis-users

Re: [postgis-users] Counting intersecting polygons

[Brent Wood]

At a guess, you are asking for the count(*) from both tables... you only want 
the count from table 1.


SELECT COUNT(Table_1.wkb_geometry) FROM Table_1,Table_2 WHERE 
ST_Intersects(Table_1.wkb_geometry,Table_2.wkb_geometry);


Brent Wood

Programme leader: Environmental Information Delivery
NIWA
DDI:  +64 (4) 3860529


[cid:imagee12d55.PNG@cdb06479.44912a6c]<http://www.niwa.co.nz>


Brent Wood
Principal Technician - GIS and Spatial Data Management
Programme Leader - Environmental Information Delivery
T +64-4-386-0529

National Institute of Water & Atmospheric Research Ltd (NIWA)
301 Evans Bay Parade, Greta Point, Wellington
Connect with NIWA: niwa.co.nz<https://www.niwa.co.nz> 
Facebook<https://www.facebook.com/nzniwa> Twitter<https://twitter.com/niwa_nz> 
LinkedIn<https://www.linkedin.com/company/niwa> 
Instagram<https://www.instagram.com/niwa_science>

To ensure compliance with legal requirements and to maintain cyber security 
standards, NIWA's IT systems are subject to ongoing monitoring, activity 
logging and auditing. This monitoring and auditing service may be provided by 
third parties. Such third parties can access information transmitted to, 
processed by and stored on NIWA's IT systems.



From: postgis-users  on behalf of 
Sarthak Vijay 
Sent: Monday, May 27, 2019 22:57
To: postgis-users@lists.osgeo.org
Subject: [postgis-users] Counting intersecting polygons


Hi All,
I have 2 tables containing polygons in same general area. What I want is the 
count of polygons in the first table (Table_1, represented by purple) that 
intersect with polygons in the 2nd (Table_2, represented by green).
[Screenshot from 2019-05-23 13-33-24.png]
For the above example, I want the output as 1. I have tried -
SELECT COUNT(*) FROM Table_1,Table_2 WHERE 
ST_Intersects(Table_1.wkb_geometry,Table_2.wkb_geometry);

This command returns the answer 3 which doesn't work for me.
Could someone please suggest relevant command.
Thanks in advance.

Regards,
Sarthak Vijay



___
postgis-users mailing list
postgis-users@lists.osgeo.org
https://lists.osgeo.org/mailman/listinfo/postgis-users

[postgis-users] HDF import to Postgis raster

[Brent Wood]

Hi postgis users!!

I'm exploring the option of importing HDF grids into Postgis, ideally to serve 
as WMS via Mapserver.

Never having used PG Raster before, or Mapserver to serve them, I figured I'd 
ask for advice here first!

The HDF files can be read by GDAL, so I'm assuming an ETL bash script could do 
the upload easily enough.

I've been serving WMS from Postgis vector data for several years now, so should 
be able to figure that out. I'd prefer to use WMS than WCS if possible, I'm not 
sure what the ramifications are.

Any advice, comments, examples gratefully received!


Thanks,

Brent Wood

Brent Wood

Programme leader: Environmental Information Delivery
NIWA
DDI:  +64 (4) 3860529

Brent Wood
Principal Technician - GIS and Spatial Data Management
Programme Leader - Environmental Information Delivery
+64-4-386-0529 | 301 Evans Bay Parade, Greta Point, Wellington | 
www.niwa.co.nz<http://www.niwa.co.nz>
[NIWA]<http://www.niwa.co.nz>
To ensure compliance with legal requirements and to maintain cyber security 
standards, NIWA's IT systems are subject to ongoing monitoring, activity 
logging and auditing. This monitoring and auditing service may be provided by 
third parties. Such third parties can access information transmitted to, 
processed by and stored on NIWA's IT systems.



___
postgis-users mailing list
postgis-users@lists.osgeo.org
https://lists.osgeo.org/mailman/listinfo/postgis-users

[postgis-users] Fw: HDF import to Postgis raster

[Brent Wood]

Hi postgis users!!
I'm exploring the option of importing HDF grids into Postgis, ideally to serve 
as WMS via Mapserver.
Never having used PG Raster before, or Mapserver to serve them, I figured I'd 
ask for advice here first!
The HDF files can be read by GDAL, so I'm assuming an ETL bash script could do 
the upload easily enough.
I've been serving WMS from Postgis vector data for several years now, so should 
be able to figure that out. I'd prefer to use WMS than WCS if possible, I'm not 
sure what the ramifications are.
Any advice, comments, examples gratefully received!

   Thanks,
Brent Wood

   

   ___
postgis-users mailing list
postgis-users@lists.osgeo.org
https://lists.osgeo.org/mailman/listinfo/postgis-users

Re: [postgis-users] Problem extracting SQL Server Geometry (or, what is the 0x character?)

[Brent Wood]

Hey Ben...
Long time!
In the absence of a more elegant approach...

You could try the FDW query going via WKT rather than WKB - so deconstruct then 
reconstruct?
Minor overhead for a one-off transfer, perhaps more of an issue for a working 
query.
I use both fdw & OGR virtual data source to access data from Specify (MySQL) in 
QGIS. Both work fine. Different sorts of issues with each. So fdw is not just 
used for data transfers :-)
Cheers,
  Brent

  From: Ben Madin 
 To: PostGIS Users Discussion  
 Sent: Saturday, October 15, 2016 1:03 PM
 Subject: [postgis-users] Problem extracting SQL Server Geometry (or, what is 
the 0x character?)
   
G'day all,
I hope a simple case of something I've missed, but we are trying to extract 
data from a SQL Server database into PostGIS use tds_fdw... the data in SQL 
Server appears to be in WKB - but when when connect to this field we have a 
precursor 0x. I can't find any references to anyone else suffering this 
problem, but that could be because I'm trying a lazy approach to automate 
retrieval of hundreds of tables using the FDW (that's what it is for, right?)
I'm left with a sense that it is an encoding error between the two systems? 
I've tried making the fdw column text instead of geometry, but I can't get rid 
of the 0x, and no amount of trying to cajole the text to any other form makes 
it any happier. 
To complicate it, for testing I'm going from SQL Server 2014 (running in 
Windows 8.1 in a VM) to PostgreSQL 9.4 on a Mac (El Capitan) using tds_fdw 
compiled on the same mac. POSTGIS="2.2.2 r14797" GEOS="3.5.0-CAPI-1.9.0 r0" 
PROJ="Rel. 4.9.2, 08 September 2015" GDAL="GDAL 2.1.1, released 2016/07/07" 
LIBXML="2.7.8" LIBJSON="0.12.1" RASTER
Any ideas gratefully received? 
cheers
Ben




m : +61 448 887 220
e : b...@ausvet.com.au
10 High Street, FremantleWestern Australia
on the web: www.ausvet.com.au


This transmission is for the intended for a mailing list and is clearly never 
going to be confidential information. If you have received this transmission in 
error, apologies! The contents of this email are the likely ill-educated 
opinion of the writer only and are not endorsed by Ausvet unless expressly 
stated otherwise. Thanks for reading. An even bigger thanks for any help you 
can provide.
___
postgis-users mailing list
postgis-users@lists.osgeo.org
http://lists.osgeo.org/mailman/listinfo/postgis-users

   ___
postgis-users mailing list
postgis-users@lists.osgeo.org
http://lists.osgeo.org/mailman/listinfo/postgis-users

Re: [postgis-users] How do I get 'n' points nearest another point?

[Brent Wood]

Hi Gerry,

Not optimised but essentially get your city point, get the distances to each of 
the radar points, sort on distance, return the first n (5).

select r.id fromradar r,city cwhere c.id=city point id
order by ST_Distance(c.point, r.point)limit 5;
Any way you can reduce the number of radar points to test the distance for will 
improve performance - like if the radar table has a city id column you can 
restrict the search to just those by adding and r.id = c.id to the where 
clause. 


Cheers
  Brent Wood

  From: Gerry Creager - NOAA Affiliate gerry.crea...@noaa.gov
 To: PostGIS Users Discussion postgis-users@lists.osgeo.org 
 Sent: Tuesday, June 23, 2015 7:30 AM
 Subject: [postgis-users] How do I get 'n' points nearest another point?
   
I've two tables, one with a set of city centroids, and one with radar 
locations. I need to select the 4 (or maybe 5) closes radars to a given city 
point. 
I know I've done something similar, but that was 2 or 3 major releases ago, and 
the syntax is now foreign to me!
Thanks in advance,
gerry-- 
Gerry CreagerNSSL/CIMMS405.325.6371++“Big whorls have 
little whorls,That feed on their velocity; And little whorls have lesser 
whorls, And so on to viscosity.” Lewis Fry Richardson (1881-1953)
___
postgis-users mailing list
postgis-users@lists.osgeo.org
http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users

  ___
postgis-users mailing list
postgis-users@lists.osgeo.org
http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users

Re: [postgis-users] Need help on basic concepts, I just need really simple calculations

[Brent Wood]

Hi Aaron,
Hopefully this (simplistic) description helps.

Setting a SRID value for a feature tells Postgis what coordinate reference 
system (CRS) the coordinates are in. This includes the unit, which can be any 
linear unit of measurement, such as degrees (measured at the surface of the 
earth), meters, feet, etc. Obviously getting this right is important to 
calculate distances correctly.

Different CRS's generally apply to different parts of the globe, and include 
the projection parameters. Note that every projection applies some distortion - 
trying to map (project) a part of a spherical surface to a flat plane has this 
problem. There are three main types of distortion - angular (changes shapes), 
area and distance. Normally to measure distance using a projected CRS, you'd 
use an equidistant projection (which minimises distance distortions) centered 
near the location you are trying to measure.
An alternative approach is to measure it on a spheroid, in 3D space, instead of 
on a projected 2D space. This is basically what a Postgis geography allows you 
to do. But the coordinate units in a geography are degree coordinates, so you 
need to specify that your coordinates are unprojected lon/lat values when you 
use them in a geography. The SRID (Spatial Reference ID) for such a CRS is 
4326. 

In your case, try:SELECT * FROM users
WHERE ST_DWithin (users.location::geography, st_setsrid(st_makepoint (146.0,
138.19), 4326)::geography, 100);
This assumes your location is a geometry with a SRID of 4326 (ie: the 
coordinate values are unprojected lon/lat degrees). It then converts this 
geometry to a geography datatype, which it tests against a point geometry in 
SRID 4326, which is also converted to a geography for the test, to see if it is 
within 100m. So this SQL tests geography against geography datatype.
If your location feature is not SRID 4326, you'll need to reproject (transform) 
it to 4326 to for this to work:
SELECT * FROM users
WHERE ST_DWithin (ST_Transform(users.location,4326)::geography, 
st_setsrid(st_makepoint (146.0,
138.19), 4326)::geography, 100);
(I haven't tested it, but I think this should work)
Cheers
Bent Wood

  From: Aaron Lewis the.warl0ck.1...@gmail.com
 To: postgis-users@lists.osgeo.org 
 Sent: Monday, March 23, 2015 12:09 AM
 Subject: [postgis-users] Need help on basic concepts, I just need really 
simple calculations
   
Hi,

I've been searching online for days. Trying to understand why SRID is
required. So I picked some random value.

Now I'm need to retrieve POINTs within a circle range, e.g a circle at
(146.0, 138.19) with radius of 100 meters:

SELECT * FROM users
WHERE ST_DWithin (users.location, st_setsrid(st_makepoint (146.0,
138.19), 2600), 100);

It's very simple, but the result seems wrong. I have a record contains
a POINT(55 43) that matches this query.

Anyone know what's wrong with it?

-- 
Best Regards,
Aaron Lewis - PGP: 0x13714D33 - http://pgp.mit.edu/
Finger Print:  9F67 391B B770 8FF6 99DC  D92D 87F6 2602 1371 4D33
___
postgis-users mailing list
postgis-users@lists.osgeo.org
http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users


  ___
postgis-users mailing list
postgis-users@lists.osgeo.org
http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users

Re: [postgis-users] Need help on basic concepts, I just need really simple calculations

[Brent Wood]

;
This returns six distances, cartesian degrees, cartesian degrees converted to 
km (1.852km/minute), km
projected to NZMG, km projected to the custom AEA projections, km projected to 
a sphere and km
projected to the WGS84 spheroid. Most of these give similar answers, all are 
correct answers, as far as
they go. Which is most correct is debatable. NZMG has increasing distortion the 
further from land we
go; the AEA is strictly an equal area projection, so has small inherent issues 
with distances, partly
depending on the direction between the points and distance from the centre; 
spheroidal is likely to give
a good estimate anywhere, but will vary depending on the spheroid used; 
spherical is quick but subject
to some error as the world is not a sphere. The native cartesian degree value 
is highly dependent on the
direction being measured and the distance from the equator, and is pretty 
definitely the least accurate.
When it comes to areas, the AEA projection will be reasonably reliable  
consistent around New
Zealand. To get the specified areas for each stratum, and the one calculated by 
PostGIS, using the new
AEA projection and NZMG, try this sql.
select t.trip_code,
s.stratum,
s.area_km2,
(area(transform(m.geom,27201))/100)::decimal(7,2) as AEA_area,
(area(transform(m.geom,27200))/100)::decimal(7,2) as NZMG_area,
(area(transform(m.geom,2193))/100)::decimal(7,2) as NZTM_area
from stratum s,
stratum_def m,
trip t,
stratum_trip st
where st.stratum_key = m.stratum_key
and s.stratum=st.stratum
and st.trip_code = t.trip_code
and s.trip_code=t.trip_code
order by trip_code,
stratum;



 From: Aaron Lewis the.warl0ck.1...@gmail.com
 To: Brent Wood pcr...@pcreso.com 
Cc: PostGIS Users Discussion postgis-users@lists.osgeo.org 
 Sent: Monday, March 23, 2015 2:19 PM
 Subject: Re: [postgis-users] Need help on basic concepts, I just need really 
simple calculations
   
Thanks Brent, I think I understand GIS a little bit now ...

Just two more questions,
1. When looking for points within a circle, is this the recommended way?
    Which is,
    Storing data in `location geometry(point, 4326)` , convert it to
geography then do the calculation

2. I tried to create a GIST index and it's not working ... The
document says ST_DWithin uses index anyway.

gis=# create index user_loc_gist_idx on users using gist (location);
gis=# VACUUM ANALYSE;

gis=# explain SELECT * FROM users WHERE ST_DWithin (users.location,
st_setsrid(st_makepoint (45.3, 35.2), 4326), 100);



            QUERY PLAN
--
 Seq Scan on users  (cost=0.00..1.80 rows=1 width=146)
  Filter: ((location 
'010320E610010005009A594BC050C09A594BC066E660409A29624066E660409A29624050C09A594BC050C0'::geometry)
AND ('010120E61066A646409A994140'::geometry 
st_expand(location, 100::double precision)) AND _st_dwithin(location,
'010120E61066A646409A994140'::geometry,
100::double precision))
(2 rows)







On Mon, Mar 23, 2015 at 4:14 AM, Brent Wood pcr...@pcreso.com wrote:
 Hi Aaron,

 Hopefully this (simplistic) description helps.

 Setting a SRID value for a feature tells Postgis what coordinate reference
 system (CRS) the coordinates are in. This includes the unit, which can be
 any linear unit of measurement, such as degrees (measured at the surface of
 the earth), meters, feet, etc. Obviously getting this right is important to
 calculate distances correctly.

 Different CRS's generally apply to different parts of the globe, and include
 the projection parameters. Note that every projection applies some
 distortion - trying to map (project) a part of a spherical surface to a flat
 plane has this problem. There are three main types of distortion - angular
 (changes shapes), area and distance. Normally to measure distance using a
 projected CRS, you'd use an equidistant projection (which minimises distance
 distortions) centered near the location you are trying to measure.

 An alternative approach is to measure it on a spheroid, in 3D space, instead
 of on a projected 2D space. This is basically what a Postgis geography
 allows you to do. But the coordinate units in a geography are degree
 coordinates, so you need to specify that your coordinates are unprojected
 lon/lat values when you use them in a geography. The SRID (Spatial Reference
 ID) for such a CRS is 4326.

 In your case, try:
 SELECT * FROM users
 WHERE ST_DWithin (users.location::geography

Re: [postgis-users] ST_intersects query that crosses date line boundaries

[Brent Wood]

Hi,
There are two ways to draw a line between (-179, 0)  (179 0) - the long  
short way around the earth,  Postgis can't tell which is correct. 

You  could:1. cast the geometry to a geography for the query,2. try 
ST_ShiftLongitude([geometry]) which will change it to a 0-360 longitude space 
instead of +-180 (this was written to fix some 180 issues)
3. try hard coding 0-360 longitudes in your query: 
ST_GeomFromText('MULTIPOLYGON(((179.64844 67.73477,204.96094 67.60118,198.80859 
61.8462,179.64844 67.73477)))')
Note that points in your table will also need shifting to a 0-360 space in the 
query for 2  3.

You should include a ST_SetSRID([geometry],4326) as well so Postgis knows the 
CRS of the created polygon,  your point columns should also have this set.

HTH,
Brent Wood
  From: Trang Nguyen trang.ngu...@inrix.com
 To: postgis-users@lists.osgeo.org postgis-users@lists.osgeo.org 
 Sent: Friday, February 20, 2015 10:57 AM
 Subject: [postgis-users] ST_intersects query that crosses date line boundaries
   
 !--#yiv5574049598 _filtered #yiv5574049598 {font-family:Calibri;panose-1:2 15 
5 2 2 2 4 3 2 4;}#yiv5574049598 #yiv5574049598 p.yiv5574049598MsoNormal, 
#yiv5574049598 li.yiv5574049598MsoNormal, #yiv5574049598 
div.yiv5574049598MsoNormal 
{margin:0in;margin-bottom:.0001pt;font-size:11.0pt;font-family:Calibri, 
sans-serif;}#yiv5574049598 a:link, #yiv5574049598 
span.yiv5574049598MsoHyperlink 
{color:blue;text-decoration:underline;}#yiv5574049598 a:visited, #yiv5574049598 
span.yiv5574049598MsoHyperlinkFollowed 
{color:purple;text-decoration:underline;}#yiv5574049598 
span.yiv5574049598EmailStyle17 {font-family:Calibri, 
sans-serif;color:windowtext;}#yiv5574049598 .yiv5574049598MsoChpDefault 
{font-family:Calibri, sans-serif;} _filtered #yiv5574049598 {margin:1.0in 
1.0in 1.0in 1.0in;}#yiv5574049598 div.yiv5574049598WordSection1 {}--Hi, 
I am using Postgres 9.3 and have a table with geometry columns:   startloc 
geometry(Point),   endloc geometry(Point),    When I run a query that crosses 
the date line boundary, I’m getting incorrect results. Example: SELECT * from 
od1.trip_v1_partitioned where startts=TIMESTAMP '2015-02-16T20:00:00.000Z'and 
starttsTIMESTAMP '2015-02-17T20:00:00.000Z'and endtsTIMESTAMP 
'2015-02-17T20:00:00.000Z' and ST_intersects(startloc, 
ST_MakeValid(ST_GeomFromText('MULTIPOLYGON(((179.64844 67.73477,-155.03906 
67.60118,-161.19141 61.8462,179.64844 67.73477)))')))    Would I need to change 
how my columns are stored (this would require a big migration), or is it 
possible to adjust  my query to handle this correctly?    Thanks,
Trang    
___
postgis-users mailing list
postgis-users@lists.osgeo.org
http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users

  ___
postgis-users mailing list
postgis-users@lists.osgeo.org
http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users

Re: [postgis-users] Find out if polygon is circle

[Brent Wood]

 I have many different polygons in my database and would like to know if
 there's any way to find out if a polygon has the shape of a circle. I
 searched both on Google and in the Postgis documentation but couldn't find
 someone with the same question.
The lines joining the vertices of a polygon are straight - so implicitly any 
polygon defined by a sequence of points is NOT a circle, but might approximate 
one. Also, a reprojected circle may no longer be round. Any circle can be 
defined by three points, but a triangle is not a circle :-)

If what you are asking is whether each vertex in the polygon boundary is the 
same distance from the centroid - that might be done relatively easily.
Something along the lines of (off the top of my head - this will need work to 
work):select 
count(distinct(ST_Distance(ST_Centroid(geom),((ST_DumpPoints(ST_exteriorRing(geom))).geom
 

for each polygon get the vertices, then get the distance between each vertex  
the centroid, then see how many distinct distances there are for each feature, 
if only one you have a circle - the distance being the radius.

If precision becomes a problem with near zero differences creating apparently 
different radii for the polygon, you can select where max(dist) - min(dist)  
a_very_small_number instead of all being the same. 
Cheers

Brent Wood



  ___
postgis-users mailing list
postgis-users@lists.osgeo.org
http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users

Re: [postgis-users] Convert from Lat/Long point to postGIS geometry

[Brent Wood]

I recommend you use QGIS to visualise your Postgis data  ensure it is correct 
before using Geoserver; QGIS  Postgis work very well together.
Postgis can help with the automatic populating of the data. You can create an 
on insert (or update) trigger in Postgres which will populate the missing 
column(s) whenever a record is inserted (or updated). A simple scrupt that does 
this is below - just a series of SQL's to illustrate this. Note that if you 
update a record (change x or y values) then the point will be in the wrong 
place, it needs updating as well. Ideally you should create an update  insert 
before function to replace the insert/update with a new one doing the full 
job... but this will hopefully illustrate how you might go about this.
If all your inserts/updates are done programatically rather than manually, then 
you may be able to modify the program to do this without using the db to 
automate it.
Cheers
  Brent


#! /bin/bash
# script to create database, install postgis, and create:
#   a table with a geometry column  x,y columns
#   a trigger function to update the table geometry column,
#    populating null geometries with a geometry made from coords 
#   a trigger invoking the function on update
# run a couple of inserts to test it works
# look at the result

dropdb test
createdb test
psql -d test -c create extension postgis;
psql -d test -c create table test_trigger 
   (id    serial primary key,
    x decimal(7,4),
    y decimal(7,4),
    geom  geometry(point, 4326));

psql -d test -c CREATE OR REPLACE Function update_geom() RETURNS TRIGGER AS 
 \$\$
 BEGIN 
   UPDATE test_trigger SET geom = 
ST_SetSRID(ST_Makepoint(x,y),4326) where geom isnull;
   RETURN null;
 END;
 \$\$ 
 LANGUAGE 'plpgsql';

psql -d test -c CREATE TRIGGER geom_trigger AFTER INSERT ON test_trigger FOR 
EACH ROW EXECUTE PROCEDURE update_geom();

psql -d test -c insert into test_trigger (x, y) values (179.0, -45.0);
psql -d test -c insert into test_trigger (x, y) values (179.5, -45.3);
psql -d test -c select id, x, y, ST_AsText(geom) from test_trigger;



The result of running this is:
CREATE EXTENSION
CREATE TABLE
CREATE FUNCTION
CREATE TRIGGER
INSERT 0 1
INSERT 0 1
 id |    x |    y | st_astext  
+--+--+
  1 | 179. | -45. | POINT(179 -45)
  2 | 179.5000 | -45.3000 | POINT(179.5 -45.3)
(2 rows)


  From: KhunSanAung khunsanaung@gmail.com
 To: Brent Wood pcr...@pcreso.com 
Cc: postgis-users@lists.osgeo.org postgis-users@lists.osgeo.org 
 Sent: Tuesday, February 3, 2015 9:08 PM
 Subject: Re: [postgis-users] Convert from Lat/Long point to postGIS geometry
   
Hi Brent Wood,
Many thanks, it works.UPDATE public.town SET geom = 
ST_SetSRID(ST_MakePoint(longitude, latitude), 4326);

I am using postGIS to store the data and using GeoServer for publishing the 
data to maps.
I'm thinking to use the GeoExplorer (from OpenGeo Suite) for digitizing and 
collecting the location information.When using GeoExplorer, the geometry 
information is automatically stored to the geom field of the table and the use 
have to fill all the attribute again.
But I already have the full list in a postGIS table.How can I make my 
application in such a way that user just need to select from the list and 
digitizing the location only. No need to enter the attribute again.
Many thanks for any  idea.
Best regards




On Tue, Feb 3, 2015 at 10:50 AM, Brent Wood pcr...@pcreso.com wrote:

Hi.
Try something like:

update table set geometry column = ST_SetSRID(ST_MakePoint(Longitude, 
Latitude),4326);
Essentially create a point geometry from your numeric values, with the 
ST_MakePoint() function, the inform Postgis it is a standard lat/long CS 
(EPSG:4326 - which you should have specified when you created the column),  
update the table with these values for each row. Make sure you use your table  
column names
What mapping/GIS program are you using?

Cheers,
  Brent Wood

  From: KhunSanAung khunsanaung@gmail.com
 To: postgis-users@lists.osgeo.org 
 Sent: Tuesday, February 3, 2015 5:11 PM
 Subject: [postgis-users] Convert from Lat/Long point to postGIS geometry
   
Hi All,
I have one table (Town info) in postgres without Geometry field.I have Latitude 
and Longitude information for those points data separately (collecting  
filling).

I created the postGIS extension and add the Geometry field in the above 
postgres table.Now, I'd like to add the location information into the postGIS 
geometry field so that I can immediately view those points on the map. 
How can I convert the Latitude/Longitude value into postGIS geometry value?
Thank you very much in advance.
-- 
Have a nice day!
--
Mr. Khun San Aung
___
postgis-users mailing list
postgis-users

Re: [postgis-users] Convert from Lat/Long point to postGIS geometry

[Brent Wood]

Hi.
Try something like:

update table set geometry column = ST_SetSRID(ST_MakePoint(Longitude, 
Latitude),4326);
Essentially create a point geometry from your numeric values, with the 
ST_MakePoint() function, the inform Postgis it is a standard lat/long CS 
(EPSG:4326 - which you should have specified when you created the column),  
update the table with these values for each row. Make sure you use your table  
column names
What mapping/GIS program are you using?

Cheers,
  Brent Wood

  From: KhunSanAung khunsanaung@gmail.com
 To: postgis-users@lists.osgeo.org 
 Sent: Tuesday, February 3, 2015 5:11 PM
 Subject: [postgis-users] Convert from Lat/Long point to postGIS geometry
   
Hi All,
I have one table (Town info) in postgres without Geometry field.I have Latitude 
and Longitude information for those points data separately (collecting  
filling).

I created the postGIS extension and add the Geometry field in the above 
postgres table.Now, I'd like to add the location information into the postGIS 
geometry field so that I can immediately view those points on the map. 
How can I convert the Latitude/Longitude value into postGIS geometry value?
Thank you very much in advance.
-- 
Have a nice day!
--
Mr. Khun San Aung
___
postgis-users mailing list
postgis-users@lists.osgeo.org
http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users

  ___
postgis-users mailing list
postgis-users@lists.osgeo.org
http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users

Re: [postgis-users] Creating a DBlink to load data from Oracle Spatial to PostGIS

[Brent Wood]

Can't help with plain dblink but we have succeeded with ODBC connections  
shell scripts which pipe the result from an Oracle SQL query into a Postgis SQL 
to transfer the data.
We have not pursued another approach which we feel is probably viable, which is 
connecting directly from Oracle to push data, see (amongst several such 
articles): 
https://dbaspot.wordpress.com/2013/05/29/how-to-access-postgresql-from-oracle-database/
you can also try the Oracle FDW:http://laurenz.github.io/oracle_fdw/
 http://pgxn.org/dist/oracle_fdw/


HTH,
  Brent Wood



   

   From: Tahir Tamba tahir.ta...@gmail.com
 To: PostGIS Users Discussion postgis-users@lists.osgeo.org 
 Sent: Friday, January 30, 2015 9:22 AM
 Subject: [postgis-users] Creating a DBlink to load data from Oracle Spatial to 
PostGIS
   
Hi,

I have a mapping project for generating an atlas from multiple layers from QGIS 
project. All my layers are stored in PostGIS. For cons, I have many layers to 
add that are stored in Oracle Spatial database and must be part of the project. 
This tables are updated in live every day. This is the reason why I don't have 
any choice to access  the data from Oracle database. I already have privileges 
to access data from Oracle Spatial schema that contains spatial tables.

 My question is the following . Is it possible to make a DBLink to load the 
Oracle Spatial database  tables in PostGIS database? If yes  what is the 
procedure and steps for loading Oracle Spatialtables into PostgreSQL / PostGIS ?

Otherwise, are there another ways to load tables from Oracle Spatial database 
to PostGIS ?

Any suggestions would be greatly appreciated!

Regards!

___
postgis-users mailing list
postgis-users@lists.osgeo.org
http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users

  ___
postgis-users mailing list
postgis-users@lists.osgeo.org
http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users

Re: [postgis-users] Evenly distributing a point set

[Brent Wood]

Replied at  
http://gis.stackexchange.com/questions/131854/spacing-a-set-of-pointswith some 
Postgis options...
Brent Wood
 From: toni hernández t...@sigte.udg.edu
 To: postgis-users@lists.osgeo.org 
 Sent: Wednesday, January 28, 2015 12:05 AM
 Subject: Re: [postgis-users] Evenly distributing a point set
   
 Maybe not on a point.
 I was thinking with linestring
 
 

On 27/01/2015 11:46, Dave Barter wrote:
  
 #yiv5480649879 body{font-family:Helvetica, Arial;font-size:13px;} 
  
  Maybe you can also try to simplify the geometry using X as the tolerance. 
 http://postgis.net/docs/manual-1.4/ST_Simplify.html 
 
 On 25/01/2015 12:54, Dave Barter wrote: 
  KNN distance 
 
 
   
 
 Will that work on a point layer? I have added a diagram and more detail here 
http://gis.stackexchange.com/questions/131854/spacing-a-set-of-points 
  
 ___
postgis-users mailing list
postgis-users@lists.osgeo.org
http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users 
 
 
___
postgis-users mailing list
postgis-users@lists.osgeo.org
http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users

  ___
postgis-users mailing list
postgis-users@lists.osgeo.org
http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users

Re: [postgis-users] Creating trajectory/lines from millions of points[PostGIS]

[Brent Wood]

or automatically get the start  end times for each trackline in the record 
like this:

WITH multis AS (    SELECT id, min(time_field) AS time_start, 
max(time_field) as time_end, status, ST_MakeLine(array_agg(point_geom )) AS 
mylines    FROM your_tableGROUP BY id, statusORDER BY time_field)  
SELECT id, status, (ST_Dump(mylines)).geomFROM multis;

Cheers,   Brent Wood
  From: Hugues François hugues.franc...@irstea.fr
 To: PostGIS Users Discussion postgis-users@lists.osgeo.org 
 Sent: Tuesday, November 25, 2014 8:13 PM
 Subject: Re: [postgis-users] Creating trajectory/lines from millions of 
points[PostGIS]
   
#yiv8453726355 #yiv8453726355 -- _filtered #yiv8453726355 
{font-family:Calibri;panose-1:2 15 5 2 2 2 4 3 2 4;} _filtered #yiv8453726355 
{font-family:Tahoma;panose-1:2 11 6 4 3 5 4 4 2 4;}#yiv8453726355 
#yiv8453726355 p.yiv8453726355MsoNormal, #yiv8453726355 
li.yiv8453726355MsoNormal, #yiv8453726355 div.yiv8453726355MsoNormal 
{margin:0cm;margin-bottom:.0001pt;font-size:12.0pt;}#yiv8453726355 a:link, 
#yiv8453726355 span.yiv8453726355MsoHyperlink 
{color:blue;text-decoration:underline;}#yiv8453726355 a:visited, #yiv8453726355 
span.yiv8453726355MsoHyperlinkFollowed 
{color:purple;text-decoration:underline;}#yiv8453726355 p 
{margin-right:0cm;margin-left:0cm;font-size:12.0pt;}#yiv8453726355 
span.yiv8453726355EmailStyle18 {color:#1F497D;}#yiv8453726355 
.yiv8453726355MsoChpDefault {} _filtered #yiv8453726355 {margin:70.85pt 70.85pt 
70.85pt 70.85pt;}#yiv8453726355 div.yiv8453726355WordSection1 {}#yiv8453726355 
Hello,  In your case I would have try to make multilines for each taxi and each 
status (i.e. two multi by taxi) and then dump them into  simple linestrings. 
All in a query that may look like this assuming you have a taxi id field:  WITH 
multis AS (    SELECT id, status, ST_MakeLine(array_agg(point_geom 
)) AS mylines    FROM your_tableGROUP BY id, statusORDER BY 
time_field)  SELECT id, status, (ST_Dump(mylines)).geomFROM multis  You may 
want to add a time reference to your lines. To do this, you can add an 
extraction from your timestamp field (e.g. day or month) and add it into the 
WITH and to the group by clause.  Hugues.          De : 
postgis-users-boun...@lists.osgeo.org 
[mailto:postgis-users-boun...@lists.osgeo.org] De la part de Oliver Burgfeld
Envoyé : mardi 25 novembre 2014 07:09
À : postgis-users@lists.osgeo.org
Objet : [postgis-users] Creating trajectory/lines from millions of 
points[PostGIS]  

Hi,I have millions of points in a PostGIS database containing taxi gps tracks. 
Now I want to create lines from these points by vehicleid and ordered by 
timestamp. But, and that's my problem right now, at first I want to include 
every column of my point table into the line table and I also need to 
intersect those lines at specific points.I have one column representing the 
taxi_is_occupied status with 0 or 1. What I want now is to create lines which 
are divided every time this status changes. In the end I need lines which show 
the path of every taxi over time, divided every time the status of the car 
changes so that I can query all lines where the taxi is occupied, for 
example.What do I have to use therefore? I know that there is the ST_MakeLines 
tool existing in PostGIS, but as I am a new PostGIS user... I do not know 
exactly how to use it to get the results I need.   Thanks a lot
___
postgis-users mailing list
postgis-users@lists.osgeo.org
http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users

  ___
postgis-users mailing list
postgis-users@lists.osgeo.org
http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users

Re: [postgis-users] Creating trajectory/lines from millions of points[PostGIS]

[Brent Wood]

as in my previous reply, I figured that would be useful...
WITH multis AS (    SELECT id, status, min(timestamp) as 
time_start, max(timestamp) as time_end, ST_MakeLine( point_geom ORDER BY 
timestamp) AS mylines    FROM your_tableGROUP BY id, status) SELECT 
id, status, (ST_Dump(mylines)).geomFROM multisBrent Wood
  From: Oliver Burgfeld oliver.burgf...@gmail.com
 To: postgis-us...@googlegroups.com 
Cc: pcr...@pcreso.com; postgis-users@lists.osgeo.org; 
postgis-users@lists.osgeo.org; remi.c...@gmail.com 
 Sent: Wednesday, November 26, 2014 5:10 AM
 Subject: Re: [postgis-users] Creating trajectory/lines from millions of 
points[PostGIS]
   
Thank you and all the others who were answering :)

I tried that and it seems that its working. Nevertheless I only tried it with a 
small part of my data (round about 1 million rows out of ~500 million) but if 
it's working now, it should also work with the whole dataset.

Is there a way to also include the time_field into the result? I created a new 
table with this statement given but there are only two columns (vehicleid and 
status) included. 
I know thats logical because I only included those two into my select clause 
but it would be great to not only order by time but also have a time column in 
my table.

For example:

vehicleid | status | time_start | time_end


I hope its understandable and not to mixed up...

Thanks!


Am Dienstag, 25. November 2014 16:06:33 UTC+1 schrieb Rémi Cura:
Hey, a small correction :
ST_MakeLine is already an aggregate, and you may want to enforce the order 
inside the aggregate (see at the end).
Another interesting point is the possiblity to pu somehting in the M value of 
each point of the line, for instance the time.
This comes very handy when you want to extrat parts of the lines.


So for instance for the first proposition :

WITH multis AS (    SELECT id, status, ST_MakeLine( point_geom 
ORDER BY time_field) AS mylines    FROM your_tableGROUP BY id, 
status) SELECT id, status, (ST_Dump(mylines)).geomFROM multis

Cheers,
Rémi-c


2014-11-25 9:53 GMT+01:00 Brent Wood pcr...@pcreso.com:

or automatically get the start  end times for each trackline in the record 
like this:

WITH multis AS (    SELECT id, min(time_field) AS time_start, 
max(time_field) as time_end, status, ST_MakeLine(array_agg(point_ geom )) AS 
mylines    FROM your_tableGROUP BY id, statusORDER BY time_field)  
SELECT id, status, (ST_Dump(mylines)).geomFROM multis;

Cheers,   Brent Wood
  From: Hugues François hugues@irstea.fr
 To: PostGIS Users Discussion postgi...@lists.osgeo.org  
 Sent: Tuesday, November 25, 2014 8:13 PM
 Subject: Re: [postgis-users] Creating trajectory/lines from millions of 
points[PostGIS]
   
Hello,  In your case I would have try to make multilines for each taxi and each 
status (i.e. two multi by taxi) and then dump them into  simple linestrings. 
All in a query that may look like this assuming you have a taxi id field:  WITH 
multis AS (    SELECT id, status, ST_MakeLine(array_agg(point_ geom 
)) AS mylines    FROM your_tableGROUP BY id, statusORDER BY 
time_field)  SELECT id, status, (ST_Dump(mylines)).geomFROM multis  You may 
want to add a time reference to your lines. To do this, you can add an 
extraction from your timestamp field (e.g. day or month) and add it into the 
WITH and to the group by clause.  Hugues.          De : postgis-us...@lists. 
osgeo.org [mailto:postgis-us...@ lists.osgeo.org] De la part de Oliver Burgfeld
Envoyé : mardi 25 novembre 2014 07:09
À : postgi...@lists.osgeo.org
Objet : [postgis-users] Creating trajectory/lines from millions of 
points[PostGIS]  

Hi,I have millions of points in a PostGIS database containing taxi gps tracks. 
Now I want to create lines from these points by vehicleid and ordered by 
timestamp. But, and that's my problem right now, at first I want to include 
every column of my point table into the line table and I also need to 
intersect those lines at specific points.I have one column representing the 
taxi_is_occupied status with 0 or 1. What I want now is to create lines which 
are divided every time this status changes. In the end I need lines which show 
the path of every taxi over time, divided every time the status of the car 
changes so that I can query all lines where the taxi is occupied, for 
example.What do I have to use therefore? I know that there is the ST_MakeLines 
tool existing in PostGIS, but as I am a new PostGIS user... I do not know 
exactly how to use it to get the results I need.   Thanks a lot
__ _
postgis-users mailing list
postgi...@lists.osgeo.org
http://lists.osgeo.org/cgi- bin/mailman/listinfo/postgis- users

   
__ _
postgis-users mailing list
postgi...@lists.osgeo.org


http://lists.osgeo.org/cgi- bin/mailman/listinfo/postgis- users

Re: [postgis-users] st_extent crossing international date line

[Brent Wood]

Hi Christian,

Still use ST_Shift_Longitude()... it works best with points - polygons  
linestrings can have topological issues that it doesn't address.

SELECT st_extent(
test(# ST_GeomFromText('MULTIPOINT(162.06 56.144, -140.808 66.07, -153.301 
57.36)',4326));
 st_extent 
---
 BOX(-153.301 56.144,162.06 66.07)
(1 row)

test=# SELECT st_extent(ST_Shift_Longitude(
ST_GeomFromText('MULTIPOINT(162.06 56.144, -140.808 66.07, -153.301 
57.36)',4326)));
    st_extent 
--
 BOX(162.06 56.144,219.192 66.07)
(1 row)



It depends if you want a 0-360 or +-180 extent... 


A better solution would perhaps be if ST_Extent() worked with the geography 
datatype - but the point sequence would need to implicitly determine the 
polygon extent across 180.

To be robust, you could maybe calculate the 0-360 and +-180 extents,  take the 
one with the smaller area?


HTH,


Brent Wood




 From: Christian Gendreau christiangendr...@gmail.com
To: postgis-users@lists.osgeo.org 
Sent: Friday, May 23, 2014 3:05 AM
Subject: [postgis-users] st_extent crossing international date line
 


Hi there,

I was wondering how can we perform an extent over the international date line?

ST_Shift_Longitude seems perfect to compare 2 polygons over the IDL but what if 
I have 3 points (let say 2 in Alaska and 1 in Russia) and I want the get the 
extent?

e.g. SELECT st_extent(ST_GeomFromText('MULTIPOINT(162.06 56.144, -140.808 
66.07, -153.301 57.36)',4326));

Returns: BOX(-153.301 56.144,162.06 66.07), the longitude -140 was no included 
due to IDL.

Regards,

Christian Gendreau
___
postgis-users mailing list
postgis-users@lists.osgeo.org
http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users___
postgis-users mailing list
postgis-users@lists.osgeo.org
http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users

Re: [postgis-users] xkcd on map clocks...

[Brent Wood]

Couldn't resist - he's done it again - a novel map for (sort of) 
spatio-temporal data


http://xkcd.com/1335/

Brent




 From: Gerry Creager - NOAA Affiliate gerry.crea...@noaa.gov
To: PostGIS Users Discussion postgis-users@lists.osgeo.org 
Sent: Wednesday, February 26, 2014 12:30 PM
Subject: [postgis-users] xkcd
 


In case you have time for a little frivolity... https://xkcd.com/327/

-- 

Gerry Creager
NSSL/CIMMS
405.325.6371
++
“Big whorls have little whorls,
That feed on their velocity; 
And little whorls have lesser whorls, 
And so on to viscosity.” 
Lewis Fry Richardson (1881-1953)
___
postgis-users mailing list
postgis-users@lists.osgeo.org
http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users___
postgis-users mailing list
postgis-users@lists.osgeo.org
http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users

Re: [postgis-users] PostGIS user interface

[Brent Wood]

Hi Robert,

Postgis is not a complete GIS tool such as you describe. It is an add-on to the 
Postgresql relational database to support the management of spatial data - a 
(very powerful) spatially enabled database. (it is also often used to describe 
a Postgres database with Postgis installed - so could mean both the addon 
toolset  the finished database - which can get confusing, especially when it 
is not actually a complete GIS system) 


This could very easily manage the sorts of information you describe, in a 
database, but it has no map display capabilities. I recommend the combination 
of Postgis as the data management tool, coupled with QGIS as the desktop 
mapping (GIS) tool to allow you to see the Postgis data on a map, and click 
on it to view the linked attribute data. You can also use other tools to 
provide web based access via web services to the Postgis database - pretty much 
whatever you might want to do with spatial data can be done with some 
combination of open source tools like these.


Cheers,

 Brent Wood




 From: rkoeh...@excellandservices.com rkoeh...@excellandservices.com
To: postgis-users@lists.osgeo.org 
Sent: Wednesday, February 26, 2014 3:01 PM
Subject: [postgis-users] PostGIS user interface
 


Hello, 

Are there versions of PostGIS that can be used as a database input interface to 
capture land owner information for Land Acquisition companies? (Right-of-Way 
acquisition).  Our right of way company is looking for a GIS solution is which 
we can capture GPS coordinate data, land owner information easement 
documentation, notes, etc. and attach it to specific parcels, so that when we 
click on a parcel the data will pop up.  e.g. easement document.  

Thank you, 


Robert 651-210-9111




Robert Koehler
Senior Siting and Land Rights Agent
Excel Land Services
rkoeh...@excellandservices.com
651-253-0003

 

___
postgis-users mailing list
postgis-users@lists.osgeo.org
http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users___
postgis-users mailing list
postgis-users@lists.osgeo.org
http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users

Re: [postgis-users] xkcd

[Brent Wood]

Hi Gerry,

From a GIS perspective, I also like this one - even though my favourite one 
isn't listed!


Brent Wood




 From: Gerry Creager - NOAA Affiliate gerry.crea...@noaa.gov
To: PostGIS Users Discussion postgis-users@lists.osgeo.org 
Sent: Wednesday, February 26, 2014 12:30 PM
Subject: [postgis-users] xkcd
 


In case you have time for a little frivolity... https://xkcd.com/327/

-- 

Gerry Creager
NSSL/CIMMS
405.325.6371
++
“Big whorls have little whorls,
That feed on their velocity; 
And little whorls have lesser whorls, 
And so on to viscosity.” 
Lewis Fry Richardson (1881-1953)
___
postgis-users mailing list
postgis-users@lists.osgeo.org
http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users___
postgis-users mailing list
postgis-users@lists.osgeo.org
http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users

Re: [postgis-users] xkcd

[Brent Wood]

Apologies - that was less than useful!!

http://xkcd.com/977/


Brent Wood.




 From: Gerry Creager - NOAA Affiliate gerry.crea...@noaa.gov
To: PostGIS Users Discussion postgis-users@lists.osgeo.org 
Sent: Wednesday, February 26, 2014 12:30 PM
Subject: [postgis-users] xkcd
 


In case you have time for a little frivolity... https://xkcd.com/327/

-- 

Gerry Creager
NSSL/CIMMS
405.325.6371
++
“Big whorls have little whorls,
That feed on their velocity; 
And little whorls have lesser whorls, 
And so on to viscosity.” 
Lewis Fry Richardson (1881-1953)
___
postgis-users mailing list
postgis-users@lists.osgeo.org
http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users___
postgis-users mailing list
postgis-users@lists.osgeo.org
http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users

Re: [postgis-users] Geoportal Server vs Postgres Geoportal

[Brent Wood]

Hi Nicholas,

We use Geonetwork as an online catalogue - if the record describes a spatial 
dataset, we have links to (for example - depends on what is available) a 
geotiff, a shapefile to download, a WFS service, a WMS service, a spreadsheet, 
a report/paper describing the dataset, presentations, etc.

You can create the files any way you like, but Geonetwork is (IMHO) a more 
powerful, interoperable  standards compliant tool than ESRI's Geoportal. In 
the areas involving environmental information where I work, I'd suggest 
Geonetwork is far more the standard tool than Geoportal, which is only really 
useful for ESRI users  spatial datasets - related aspatial content is not so 
well supported by Geoportal - yet spatial datasets often have related documents 
it is useful to provide as well.

We also embed such catalogues in web portals, so that projects can be well 
documented with relavant reports, data, presentations, online maps etc, are all 
readily available.


One of our catalogues: see data  reports at (fully open source based):
http://www.os2020.org.nz/bay-of-islands-coastal-survey-project/
https://secure.niwa.co.nz/boi_dc/srv/en/main.home

For Geonetwork, see:

http://geonetwork-opensource.org/
http://geonetwork-opensource.org/gallery/gallery.html



Alternatively, some OGC server products support other formats as well as the 
OGC ones - including shapefiles...

UMN mapserver uses OGR to generate content - so formats supported by OGR are 
supported, including shapefiles built on the fly  zipped up...  

http://mapserver.org/output/ogr_output.html#ogr-output
in the mapfile you define a shapefile as a supported format:
OUTPUTFORMAT 

  NAME SHAPEZIP
  DRIVER OGR/ESRI Shapefile 

  FORMATOPTION STORAGE=memory 

  FORMATOPTION FORM=zip 

  FORMATOPTION FILENAME=result.zip
 END

Geoserver also allows layers to be downloaded as zipped up shapefiles rather 
than OGC Web Services:
https://wiki.state.ma.us/confluence/display/massgis/GeoServer+-+WFS+-+Extract+-+Shapefile+Format
you specify the format as a parameter in the URL:...outputformat=SHAPE-ZIP


Hope this helps, no shortage of Open Source options to deliver data as 
shapefiles :-)

(I'm off to sea for a month in a couple of hours - so will not be able to 
respond to further emails until Feb...)


Cheers,

   Brent Wood





 From: Ben Madin b...@ausvet.com.au
To: PostGIS Users Discussion postgis-users@lists.osgeo.org 
Sent: Tuesday, December 31, 2013 7:37 PM
Subject: Re: [postgis-users] Geoportal Server vs Postgres Geoportal
 

G’day Nicholas,

We typically use a workflow like :

1. create a table with the desired geometry;
2. use pgsql2shp to dump it as a shapefile;
3. run shptree over it to create a .qix index (for mapserver and qgis, maybe 
not really necessary);
4. zip the files into an archive;
5. move them to a web accessible directory;
6. change the permissions to allow download.

I’d note that we have recently started using ogr2ogr as pgsql2shp has been 
stopping about 5 records short of our typical full dataset (global first level 
administrative districts, with half of Zimbabwe missing!) ogr2ogr is a bit more 
complex, but can also output other file formats, not just shape file. You can 
also provide a query (not just a table name) but we don’t do it that way.

cheers

Ben






On 2013-12-23, at 08:45 , Nicholas Tapia tapia.nicho...@gmail.com wrote:

 If this is the wrong place to ask this question, please point me in the right 
 direction!
 
 I'm very new to databases and GIS.  I'm researching geoportals and how they 
 offer geometries for download.
 
 As I understand it, Esri's open source Geoportal Server is the standard 
 method of offering data for download (besides offering shape files for 
 download as a file...like the census website).  It is a software layer on top 
 of the database that allows you to select the geometries you want by drawing 
 a polygon.  It also manages metadata and offers some search methods.
 
 But it doesn't allow me to make awesome sql queries.  So I want to use 
 pgsql2shp to allow people to download the geometries.   Are there any reasons 
 why I shouldn't offer geometry downloads from a postgres database using 
 pgsql2shp?
 
 Also, are there any examples of what I'm talking about now?  Are there any 
 postgres dbs that allow for direct download of geometries? And don't use esri 
 geoportal server?
 
 Thanks!
 
 -Nicholas
 
 ___
 postgis-users mailing list
 postgis-users@lists.osgeo.org
 http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users


-- 

Ben Madin

t : +61 8 6102 5535
m : +61 448 887 220
e : b...@ausvet.com.au

AusVet Animal Health Services
Western Australia

AusVet's website:  http://www.ausvet.com.au

This transmission is for the intended addressee only and is confidential 
information. If you have received this transmission in error, please delete it 
and notify the sender. The contents of this email

Re: [postgis-users] Finding Islands

[Brent Wood]

I figure you have spatially indexed the polygons already?


Any way of pre-categorising your polygons - binning them in some way that 
allows a non spatial test in the where clause to replace the spatial test...

eg: a boolean attribute set to indicate if a feature has any part = a 
particular x value or not.

run this against the 2m features once to populate it, and assuming you get a 
50/50 split of T/F features, your 2m^2 query can instead include a where 
a.bool = b.bool, as we already know that if the boolean flag is different, 
they cannot touch, so your query should involve a spatial test only over 1m^2 
instead... if you do this on both x  y, the boolean filter will replace even 
more spatial calculations  drop them to 500,000^2 tests...

If you can pre-classify features so that non-spatial tests can reduce the 
spatial ones (esp for features with lots of vertices) in a where clause, such 
queries do run much faster, but you have the trade-off of the time taken to 
carry out the classification... which is only n*no_classes/2, generally much 
faster than n^n


Hope this makes sense...

Brent Wood



 From: Lee Hachadoorian lee.hachadooria...@gmail.com
To: PostGIS Users postgis-us...@postgis.refractions.net 
Sent: Wednesday, November 20, 2013 8:52 PM
Subject: [postgis-users] Finding Islands
 

I am trying to find islands, polygons in a (multi)polygon layer which 
are not connected to any other polygons in the same layer. What I came 
up with runs in a couple of seconds on a layer with ~1000 geometries, 
and a couple of minutes on a layer with ~23,000 geometries, but I want 
to run it on a layer of 2 million+ and it's taking a L-O-O-O-NG time, 
presumably because it's making (2 million)^2 comparisons.

What I came up with is:

SELECT a.*
FROM table a LEFT JOIN table b
     ON (ST_Touches(a.geom, b.geom))
WHERE b.gid is null

or

SELECT *
FROM table
WHERE gid NOT IN (
     SELECT DISTINCT a.gid
     FROM table a JOIN table b
         ON (ST_Intersects(a.geom, b.geom) AND a.gid != b.gid)
     )

The first variant raises NOTICE:  geometry_gist_joinsel called with 
incorrect join type. So I thought I could improve performance with an 
INNER JOIN instead of an OUTER JOIN, and came up with the second 
variant, and it does seem to perform somewhat better. Any suggestions 
for how to speed up either approach?

Best,
--Lee

-- 
Lee Hachadoorian
Assistant Professor in Geography, Dartmouth College
http://freecity.commons.gc.cuny.edu
    

___
postgis-users mailing list
postgis-users@lists.osgeo.org
http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users___
postgis-users mailing list
postgis-users@lists.osgeo.org
http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users

Re: [postgis-users] Check If Point Is In New Geometry

[Brent Wood]

Tyler,

Convert your 24 hours worth of points to a linestring
Clip the linestring by your polygons to generate separate lines per polygon per 
entry tinto the polygon
Count the vertices in each linestring to determine how long each period in each 
polygon was.

The following shell script might give you some ideas... it works fine here, but 
is a bit simplistic in terms of input data...


Cheers,

   Brent Wood



#! /bin/bash
# gps points to track, clipped by polygons, B Wood, Oct 2013

# create new database to test
dropdb gps
createdb gps
psql -d gps -c create extension postgis; 

# create table with some gps points
psql -d gps -c create table gps_points
    (id serial primary key,
 user_id    integer,
 point_time timestamp,
 gps_point  geometry(point,4326));

psql -d gps -c insert into gps_points values
    (default,
 1,
 '2013-10-01 00:00:00'::timestamp,
 ST_SetSRID(ST_MakePoint(0.0,0.0),4326)); 
psql -d gps -c insert into gps_points values
    (default,
 1,
 '2013-10-01 00:00:01'::timestamp,
 ST_SetSRID(ST_MakePoint(0.01,0.01),4326)); 
psql -d gps -c insert into gps_points values
    (default,
 1,
 '2013-10-01 00:00:02'::timestamp,
 ST_SetSRID(ST_MakePoint(0.02,0.02),4326)); 
psql -d gps -c insert into gps_points values
    (default,
 1,
 '2013-10-01 00:00:03'::timestamp,
 ST_SetSRID(ST_MakePoint(0.03,0.03),4326)); 
psql -d gps -c insert into gps_points values
    (default,
 1,
 '2013-10-01 00:00:04'::timestamp,
 ST_SetSRID(ST_MakePoint(0.04,0.04),4326)); 
psql -d gps -c insert into gps_points values
    (default,
 1,
 '2013-10-01 00:00:05'::timestamp,
 ST_SetSRID(ST_MakePoint(0.05,0.05),4326)); 
psql -d gps -c insert into gps_points values
    (default,
 1,
 '2013-10-01 00:00:06'::timestamp,
 ST_SetSRID(ST_MakePoint(0.06,0.06),4326)); 
psql -d gps -c insert into gps_points values
    (default,
 1,
 '2013-10-01 00:00:07'::timestamp,
 ST_SetSRID(ST_MakePoint(0.07,0.07),4326)); 
psql -d gps -c insert into gps_points values
    (default,
 1,
 '2013-10-01 00:00:08'::timestamp,
 ST_SetSRID(ST_MakePoint(0.08,0.08),4326)); 
psql -d gps -c insert into gps_points values
    (default,
 1,
 '2013-10-01 00:00:09'::timestamp,
 ST_SetSRID(ST_MakePoint(0.09,0.09),4326)); 

psql -d gps -c insert into gps_points values
    (default,
 1,
 '2013-10-01 00:00:10'::timestamp,
 ST_SetSRID(ST_MakePoint(0.10,0.10),4326)); 

psql -d gps -c insert into gps_points values
    (default,
 1,
 '2013-10-01 00:00:11'::timestamp,
 ST_SetSRID(ST_MakePoint(0.11,0.11),4326)); 

psql -d gps -c insert into gps_points values
    (default,
 1,
 '2013-10-02 00:00:10'::timestamp,
 ST_SetSRID(ST_MakePoint(0.12,0.12),4326)); 


# create table  some polygons
psql -d gps -c create table polygons
    ( id serial primary key,
  name   varchar(10),
  poly   geometry(POLYGON,4326));

psql -d gps -c insert into polygons values
    (default,
 'poly1',
 ST_PolygonFromText(
  'POLYGON((0.0 0.0, 0.0 0.05, 0.05 0.05, 0.05 0.0, 0.0 0.0))',4326));

psql -d gps -c insert into polygons values
    (default,
 'poly2',
 ST_PolygonFromText(
  'POLYGON((0.05 0.05, 0.05 0.1, 0.1 0.1, 0.1 0.05, 0.05 0.05))',4326));


# query to get trackline
psql -d gps -c create table trackline as 
    SELECT gps.user_id, 
   gps.point_time::date as track_date, 
   ST_MakeLine(gps.gps_point) as trackline
   FROM (SELECT user_id, gps_point, point_time 
 FROM gps_points
 WHERE user_id = 1
   AND point_time::date = '2013-10-01'::date
 ORDER BY point_time) as gps
   GROUP BY gps.user_id,
    track_date;

# get intersections
psql -d gps -c create table segments as
    SELECT t.user_id,
   t.track_date,
   p.name,
   ST_Intersection(t.trackline, p.poly) as segment
    FROM trackline t,
 polygons

[postgis-users] ST_Union() performance problem (with possible funding)

[Brent Wood]

Hi, 


Any advice appreciated!!

I'm undertaking a spatial analysis using Postgis (what else would I use!!!). 
The first part works well.


I take a large number (potentially millions) of lines defined by start  end 
points  buffer them to create polygons. (I'm working in lat/long EPSG:4326 but 
transforming to a custom equal area projection for the buffering operation).

I generate a grid of 5x5km cells (polygons) covering the region of interest.

I clip the line based polygons to the grid, so I can generate statistics for 
each cell describing the lines that intersect with it, various quantitative 
measures such as ST_Union() the clipped line polygons to generate a footprint 
in each cell to work out how much is/is not covered, or sum the ST_Area() of 
the clipped polygons grouped by cell to calculate an aggregate cover, which can 
be several times the actual cell area.



So far so good, it works well, the code is clear  transparent  provides a 
good result. At least as good as any commercial software can do. My test data 
subset is processed from scratch in about 30 minutes.

Now I want to ST_Union() all the cell based polygons into an overall single 
multipolygon representing the footprint. The code is simple. The performance, 
even with my subset,  is a problem.

I have thousands of cell based footprint multipolygons, each potentially with 
thousands of vertices to be ST_Union()ed. Runtime is weeks for an iteration. If 
I need separate total footprints for 20 different species annually for 5 years, 
that is 100 iterations. Memory  I/O use is minimal - it is totally cpu bound.

I am looking at trying to simplify the polygons to be unioned to reduce the 
number of vertices ( hence processing) involved, but to achieve any 
significant benefit I'm having to change the shape of the polygons to 
ST_Union() too much. 



Does anyone have any suggestions as to how this could be made significantly 
faster? 
If I had $$ to throw at developers to work on the codebase (presumably GEOS?) 
could performance be significantly improved?


Thanks,

   Brent Wood
___
postgis-users mailing list
postgis-users@lists.osgeo.org
http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users

Re: [postgis-users] [postgis-devel] ST_Union() performance problem (with possiblefunding)

[Brent Wood]

These tend to assume each operation is constrained to a single cell, hence 
parallelizable, hence undertaking an operation on multiple cells concurrently.

While I'm dealing with many cells - they are all being merged into a single 
multipolygon feature. I can't load two cells into the same multipolygon at the 
same time - two writes to the same record - so can't run concurrently.


What may be possible is to perhaps run multiple processes on subsets to create 
intermediate (larger) merged polygons which can then be merged themselves to 
create the final single feature.

This would probably allow better use of resources on a multi core system... at 
present I'm using 1.7% of memory on a 100% cpu process, so I'll look into this 
approach - 8 cores running concurrently giving giving close to 8x faster is 
useful.

Brent





 From: mapl...@light42.com mapl...@light42.com
To: Brent Wood pcr...@pcreso.com; PostGIS Development Discussion 
postgis-de...@lists.osgeo.org; Bborie Park dustym...@gmail.com 
Cc: PostGIS Users Discussion postgis-users@lists.osgeo.org 
Sent: Thursday, October 17, 2013 3:16 PM
Subject: Re: [postgis-devel] ST_Union() performance problem (with   
possiblefunding)
 


yes,
true.. but with a thorough read you might notice that the gdal_retile.py
experiment was largely ineffective, 
but if you click on the link at the
top to the *next post*  
      Variable Buffers in
PostGIS
you will find the one that really worked well.. in fact, we used
that 2nd post in production for months, to great effect.
The trick on one
machine was to split to work by some constant, and then make psycopg2
connections for each bucket.

This worked very well.. 

Since then I have experimented only a tiny bit with SPARK from
the Berkeley Amp Lab for a distributed work load on a Hadoop file system, but
that world has no GEOS (yet) 

--
Brian M
Hamlin
OSGeo California
Chapter
blog.light42.com



On Wed, 16 Oct 2013
17:28:27 -0700, Bborie Park dustym...@gmail.com
wrote:

Your best bet is to consider splitting the workload among several
postgresql connections.


darkblueb had a blog post about
this...


http://blog.light42.com/wordpress/?p=23







On Wed, Oct 16, 2013 at 5:21
PM, Brent Wood pcr...@pcreso.com wrote:

Hi, 



Any advice appreciated!!


I'm undertaking a spatial analysis using Postgis (what else would I use!!!). 
The
first part works well.



I take a large number (potentially millions) of lines defined
by start  end points  buffer them to create polygons. (I'm working in
lat/long EPSG:4326 but transforming to a custom equal area projection for the
buffering operation).


I generate a grid of 5x5km cells (polygons) covering the region of
interest.


I clip the line based polygons to the grid, so I can generate statistics for
each cell describing the lines that intersect with it, various quantitative
measures such as ST_Union() the clipped line polygons to generate a footprint in
each cell to work out how much is/is not covered, or sum the ST_Area() of the
clipped polygons grouped by cell to calculate an aggregate cover, which can be
several times the actual cell area.



So far so good, it works
well, the code is clear  transparent  provides a good result. At least
as good as any commercial software can do. My test
 data subset is processed from scratch in about 30 minutes.

Now I want to ST_Union() all the cell based polygons into an overall
single multipolygon representing the footprint. The code is simple. The
performance, even with my subset,  is a problem.

I have thousands of
cell based footprint multipolygons, each potentially with thousands of vertices
to be ST_Union()ed. Runtime is weeks for an iteration. If I need separate total
footprints for 20 different species annually for 5 years, that is 100
iterations. Memory  I/O use is minimal - it is totally cpu bound.

I
am looking at trying to simplify the polygons to be unioned to reduce the number
of vertices ( hence processing) involved, but to achieve any significant
benefit I'm having to change the shape of the polygons to ST_Union() too much. 



Does anyone have any suggestions as to how this could be made
significantly faster? 
If I
 had $$ to throw at developers to work on the codebase (presumably GEOS?) could
performance be significantly improved?


Thanks,

   Brent
Wood
___
postgis-devel mailing list
postgis-de...@lists.osgeo.org
http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-devel




___
postgis-devel mailing list
postgis-de...@lists.osgeo.org
http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-devel___
postgis-users mailing list
postgis-users@lists.osgeo.org
http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users

Re: [postgis-users] ST_Overlaps broken ?

[Brent Wood]

As I understand it, ST_Overlaps() will return true for features of the same 
type which partially overlap. Where one contains another entirely (ie: 
ST_Contains() = true) ST_Overlaps() will return false.

Should you be using ST_Intersects() instead of ST_Overlaps()?

See the descriptions of the different spatial relationships here:
http://workshops.opengeo.org/postgis-intro/spatial_relationships.html

Brent Wood





 From: Jean Marchal jean.d.marc...@gmail.com
To: PostGIS Users Discussion postgis-users@lists.osgeo.org 
Sent: Wednesday, September 11, 2013 10:56 AM
Subject: [postgis-users] ST_Overlaps broken ?
 


Dear PostGIS users,


SELECT ST_Overlaps(ST_GeomFromText('POLYGON ((
    -308992.78000114113 515108.0599712543,
    -308991.140001066 515102.94997100905,
    -308984.8076443 515093.52997056395,
    -308976.323579 515071.97996953875,
    -308965.4284324 515056.24996879324,
    -308955.5937564 515039.59996800125,
    -308945.388958 515013.9399667829,
    -308940.1299986392 515003.59996629134,
    -308938.5599985644 514997.499966003,
    -308924.979213 514962.6199643463,
    -308921.439997755 514956.3899640478,
    -308910.2799972221 514944.63996349275,
    -308901.2699967995 514945.0099635087,
    -308891.5399963334 514941.33996333554,
    -308873.2199954614 514932.08996289596,
    -308858.2199947536 514918.0699622296,
    -308844.9299941212 514908.16996175796,
    -308839.5499938652 514913.7899620272,
    -308834.6499936357 514912.4499619603,
    -308829.0399933681 514907.06996170804,
    -308827.3899932876 514901.95996146277,
    -308828.86999335885 514895.079961136,
    -308828.7699933499 514882.09996052086,
    -308820.27999295294 514860.54995949566,
    -308817.84999283403 514852.3899591081,
    -308806.34999229014 514816.65995741263,
    -308803.40999215096 514801.48995669186,
    -308797.2499918565 514775.1099554375,
    -308797.518729 514770.139955204,
    -308795.6499917805 514755.0299544856,
    -308796.6799918264 514740.12995377555,
    -308796.6499918252 514726.15995311365,
    -308791.9299916029 514707.8599522449,
    -308790.5599915385 514698.7899518125,
    -308791.0399915576 514691.82995148376,
    -308789.53999149054 514684.7399511449,
    -308790.7099915445 514667.84995034337,
    -308788.3499914333 514658.7100499086,
    -308791.01999156177 514648.9100494459,
    -308791.43999157846 514642.9500491619,
    -308795.169991754 514632.23004865274,
    -308807.05999232084 514619.0800480284,
    -308814.426761 514612.6100477204,
    -308818.9499928877 514605.930047404,
    -308827.3799932897 514599.53004710004,
    -308836.2899937108 514586.1700464636,
    -308845.5799941495 514581.8200462572,
    -308857.3471605 514569.6700456813,
    -308877.12999565154 514573.0300458409,
    -308896.719996579 514578.3800460957,
    -308913.9799974039 514588.5600465797,
    -308918.66999762505 514592.8800467849,
    -308930.2199981734 514584.6900463961,
    -308937.2399985045 514584.1800463684,
    -308943.12999878824 514585.5900464356,
    -308947.75007 514590.9000466876,
    -308950.311286 514597.060046982,
    -308986.228333 514597.5600470044,
    -308995.6100012809 514606.19004741684,
    -309028.27000282705 514653.37004965544,
    -309042.7600035146 514660.3600499891,
    -309043.7500035614 514660.4300499931,
    -309048.1700037718 514668.7199503854,
    -309049.6383854 514705.7499521449,
    -309059.2800043002 514724.3899530284,
    -309071.148667 514741.17995382845,
    -309086.95000561327 514757.2499545887,
    -309096.71000608057 514774.8899554275,
    -309113.04000685364 514812.94995723665,
    -309121.7300072685 514831.51995811984,
    -309121.28000724316 514852.44995911047,
    -309122.88000731915 514872.5299600661,
    -309128.4600075856 514892.8799610324,
    -309127.0200075209 514913.73996202275,
    -309121.6700072661 514933.3299629539,
    -309109.4300066829 514951.4499638155,
    -309095.42000602186 514966.4399645254,
    -309083.1800054386 514984.559965387,
    -309081.1200053394 515014.35996680334,
    -309068.7200047523 515020.4899670929,
    -309068.3472814 515026.4499673769,
    -309067.0300046727 515030.3599675633,
    -309054.87000409514 515047.47996837646,
    -309043.9700035751 515060.69996900484,
    -309041.57000345737 515066.5199692808,
    -309032.730003044 515078.8799698688,
    -309016.76000228524 515107.71997123584,
    -309009.46000193805 515112.20997145027,
    -309004.43000169843 515112.8599714823,
    -309000.450001508 515112.5799714662,
    -308992.78000114113 515108.0599712543
    ))'), ST_Geomfromtext('POLYGON

Re: [postgis-users] Analyze a timeline of geographical events

[Brent Wood]

Hi Lorenzo,

It is not clear what the geometry type of a waypoint_sessione is. I'm guessing 
it is a point, with each point joined by sessione_id to a sessione table.


The polyline approach you mention should work for you. It is not that difficult 
to implement as a script, might be trickier as a Postgres UDF.


Turn your session points into a (multi?)linestring.

Clip the linestring by the buffer around the point. This will give a linestring 
for each seperate period the session track came within the buffer distance of 
the point. 

For each returned linestring, select the points that match your requirements 
(7km/hr) that intersect the linestring (or perhaps a very small buffer of it - 
point/line intersections can be tricky - but this case should be OK), and from 
these, select the min/max times.  

If these are more than 10 mins apart, check that there are no points within 
this interval with speeds of  7km (if so, then this sequence fails to meet 
your criteria), 

If not, you have a positive result.

HTH,

  Brent Wood




 From: Lorenzo Perone lorenzo.per...@gmail.com
To: postgis-users@lists.osgeo.org 
Sent: Friday, August 2, 2013 1:09 AM
Subject: [postgis-users] Analyze a timeline of geographical events
 


Hi,
I'm trying to resolve a not easy (for me) problem.
We are developing a GPS tracking system based on android phones.
The waypoints transmitted by the devices are stored in a Postgis table called 
waypoint_sessione.
The table has this structure.

TABLE waypoint_sessione
 gid bigserial 
 sessione_id bigint number of the session opened by the device (depends by 
user, truck, device)
 time_dataora bigint (unix timestamp)
 elevazione double precision
 direzione double precision
 velocita double precision (speed)
 pdop double precision
 precisione integer
 the_geom geometry
 
My scope is to discover for each session if a user have spent a lot of time, 
more than 10 minutes, stopped whitin a 150 m buffer from known point.
When a device is stopped his speed is not zero, so I need to use a speed 
threshold, tipically 7 Km/h.

I've thought to proceed in this way:
- Create a buffer form the known point
- Select the waypoints in the buffer grouping them by sessione_id

Here is the first doubt.
If the user, it's easy, pass through the buffer more than one time for each 
session I've to group the waypoints non only by session_id but also for each 
passage.
I can't use the gid column because in waypoint_sessione are stored datas from 
al large number of devices that are transmitting simultaneous. 
I could create a polyline for each session and then trim it by the buffer and 
select the waypoints that are the same of the node of the polyline for each 
part.
Is there a simpler way?

The step forward is, for each group of waypoints, to discover if I have a 
consecutive period of more than 10 minutes during wich the speed is below the 
threshold of 7 Km/h.
Discover these periods is my goal.

Thanks.
lorenzo

Lorenzo Perone
photoblog: http://lorenzoperone.wordpress.com
website: http://blog.spaziogis.it
GEO+ geomatica in Italia http://bit.ly/GEOplus

___
postgis-users mailing list
postgis-users@lists.osgeo.org
http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users___
postgis-users mailing list
postgis-users@lists.osgeo.org
http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users