from:"Burgholzer, Robert \(DEQ\)"

Re: [postgis-users] Spatially ordering data

2017-03-02 Thread Burgholzer, Robert (DEQ)

How about:
1. Take a centroid of each shape,
2. Rounding it’s x and y coordinates to whole lat/lon values
3. Ordering by lat_round, lon_round and these values would proceed in a gridded 
fashion.  

Ex:  select oid, floor(st_x(the_geom)) as x_index, floor(st_y(the_geom)) as 
y_index from precip_noaa_daily_grid order by x_index, y_index;
  oid  | x_index | y_index
---+-+-
  2143 | -84 |  36
  2142 | -84 |  36
  2141 | -84 |  36
  2620 | -84 |  36
  2619 | -84 |  36
  2618 | -84 |  36


The only drawback is that if there were data that were added during processing, 
they may be skipped by your processor, however, if you were to set a flag to 
indicate that something was processed, you’d be covered.

Hth,
/r/b

From: postgis-users [mailto:postgis-users-boun...@lists.osgeo.org] On Behalf Of 
Jonathan Moules
Sent: Thursday, March 02, 2017 12:42 PM
To: PostGIS Users Discussion
Subject: Re: [postgis-users] Spatially ordering data

Thanks for the suggestions. Some thoughts after testing:


Darafei: (order by ST_Geohash) Interesting notion, but when implemented the 
grouping is tenuous at best. If I return 5,000 features they can be spread over 
200km (bad) or 6km (good). It'd definitely better clustered than without this, 
but I was surprised at how dispersed some of the groups were, and that may be 
too much for my purposes.
Although I see Paul's answer to this SO question 
(https://gis.stackexchange.com/questions/188879/clustering-markers-using-quadtree/189296)
 may help. I've not tested it yet.

Lars: (content_balanced_grid) The issue here is what happens to features that 
overlap the grid edges, as I only want to process any given polygon once, and I 
can't clip them? I'd have to use a ST_Within to only get polygons inside the 
CBG (which is fine), but the remaining features would need an ST_Overlaps 
against the entire grid. I've been running that 30 mins so far on a small 
sample of my full database, and it's still running, so it definitely won't 
scale.

Remi: I was looking for something higher level. Implementing a Quad-Tree in SQL 
isn't something I'd be comfortable doing. Although if it is simple to do, it 
may be worth implementing as a feature within PostGIS; I'm sure others would 
also benefit from being able to easily spatially sort. I tried googling PostGIS 
quadtree but didn't find anything so I'm guessing PG doesn't implement this 
type of index by default.

Are there any other options? I may consider trying to glue together the two 
approaches, but that's going to take some thought.

Thanks,
Jonathan

 On Wed, 01 Mar 2017 12:56:58 + Travis Kirstine 
 wrote  
if you go with Remi suggestion you then can cluster the quad tree index to 
reorder records  

http://postgis.net/docs/performance_tips.html



On 1 March 2017 at 07:11, Rémi Cura  wrote:
hey,
the most classical approach would be to construct a quad tree.
You can think of it as a grid that adapts its resolution locally according to 
the data.
This is also easy to code.

Cheers,
Rémi-C

2017-03-01 12:46 GMT+01:00 Darafei "Komяpa" Praliaskouski :
Have a look here:

http://www.paulnorman.ca/blog/2016/05/improve-your-st-geohash-sorting-with-these-three-simple-tricks/
 

for simplest case, you can just order by your geom field.

ср, 1 мар. 2017 г. в 14:44, Jonathan Moules :
Hi List,
I've got a database of about 60 million spatial features that I need to run 
through a process.

The process can't do all of them at once, so I want to get the data from 
postgis in spatial chunks of up to say 500,000 features per query.

The data itself isn't evenly spatially distributed, so gridding won't work 
either, and I don't see any prospectively useful functions in the reference.

Non-spatially this would be simple:

    SELECT * FROM my_table ORDER BY some_key ASC LIMIT 50 OFFSET 0
    
Does anyone have any suggestions for how to spatially order data with PostGIS?

Thanks,
Jonathan
___
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 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] Postgres as cache and renderer for vector tile server

2016-03-21 Thread Burgholzer, Robert (DEQ)

Thanks Paul & Peter for the stimulating topics and resources!

-Original Message-
From: postgis-users [mailto:postgis-users-boun...@lists.osgeo.org] On Behalf Of 
Peter Devoy
Sent: Monday, March 21, 2016 1:34 PM
To: PostGIS Users Discussion
Subject: Re: [postgis-users] Postgres as cache and renderer for vector tile 
server

>Drupal
HI Robert, thanks for the suggestion but unfortunately I am not using Drupal in 
any projects.  Best of luck with your modules though, Mapzen's SQL functions 
may be of interest to you:
https://github.com/mapzen/vector-datasource/tree/master/data

> I think a generic HTTP cache in front of your web service would be the 
> most architecturally attractive solution, no?
Thanks Paul, I think you are on to something there.  I am very much throwing my 
self into the deep end here so this is the sort of obvious thing I can miss.

--
Peter Devoy
___
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] PostGIS over distributed worksites

2016-03-07 Thread Burgholzer, Robert (DEQ)

As Roxanne noted, the solution may vary greatly depending on whether or not you 
want Read-Only or Read-Write.

One method I have used for read-only with QGis (and Arc*) is Mapserver 
providing WFS -- it may be that MapBox could also serve this?  AFAIR the Arc 
stations would check for updates, though conectivity would sometimes be finicky.

Just a little splash of random thoughts...

From: postgis-users [postgis-users-boun...@lists.osgeo.org] on behalf of rox 
[r...@tara-lu.com]
Sent: Sunday, March 06, 2016 11:50 PM
To: postgis-users@lists.osgeo.org
Subject: Re: [postgis-users] PostGIS over distributed worksites

I'm not sure what portions / parts of your db are shared or vary on a
per seat basis, and this isn't my normal area of mucking... but I'll
throw some ideas out and let other people improve them :)

What if you set up a master database - perhaps AWS.  Set each
distributed seat to replicate down.  Updates are pushed to the master by
someone, and each distributed seat is auto-updated.

What won't work with a pure replicated server instance.. is local
changes, if you have them and so again - not my area of playing, but
what about two local databases?  One local for changes, and a second
that is pure replication.
That complicates your connection configuration and potentially your tool
interactions

Otherwise, as I understand it, barman can create a backup of one
instance and restore the full working database to another site - so you
could create a shared barman replication service where individual seats
could recover a master DB when updates are required.

just some thoughts to stir the discussion.

Roxanne

On 3/6/2016 7:21 PM, Lee Hachadoorian wrote:
> I usually use PostGIS in a localhost or a LAN setting, where I or my
> workgroup can all be connected to the database all the time.
>
> I recently set up a QGIS project using SpatiaLite with Dropbox
> syncing, because project participants were at multiple worksites.
> However, rendering--particularly rendering of QueryLayers--is quite
> slow, and QGIS often crashes. Testing shows that QGIS is much more
> responsive with PostGIS as the back end, and although it's difficult
> to prove a negative, it seems that QGIS crashes don't happen (or at
> least clearly happen less often). So I would like to transition the
> project to PostGIS.
>
> I am asking for your recommendations or experiences with setting this
> up in a way that will be fairly easy to show users who are technically
> savvy but new to database management. Some details:
>
> 1. Small workgroup (5 or so).
> 2. Spatial layers are big releases from city government (parcels,
> streets, infrastructure) which are updated at most a couple of times a
> year, often less frequently. Each spatial layer is updated on a
> different schedule by different agencies.
> 3. Data releases are in shapefile (blecch!).
> 4. Users do meet in person with some regularity.
>
> As a suggestion, I am considering just having everyone set up
> localhost installs of PostGIS. When there is a new data release, one
> person could load and clean the data, then dump the table, copy the
> dump to Dropbox, and other users could load the new table in their
> localhost database.
>
> Obviously this solution is not scalable--more users or more frequent
> updates would make it quite tedious--but it may work for this use
> case. I am open to criticisms and suggestions.
>
> Thanks,
> --Lee
>

___
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] LineString within Polygon

2016-03-02 Thread Burgholzer, Robert (DEQ)

IT is only a matter of parameter order:

ST_within( line, polygon)

ST_Contains(polygon, line)

hth,
/r/b

From: postgis-users [postgis-users-boun...@lists.osgeo.org] on behalf of 
Mustafa Elbehery [elbeherymust...@gmail.com]
Sent: Wednesday, March 02, 2016 10:06 AM
To: PostGIS Users Discussion
Subject: [postgis-users] LineString within Polygon



Hi All,

I want to make sure of what I am doing well.

I want to construct a geometry(LineString) from List of Points which represent 
the Path between Source && Destination Points.

Afterthen, would like to know if the path fully within a polygon.

What is the correct method, ST_within, or ST_Contains ?!!

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

Re: [postgis-users] Export from postGIS to ESRI Personal GeoDatabase

2014-12-03 Thread Burgholzer, Robert (DEQ)

Oh - and yes George, by links I meant foreign keys... I must need some sleep 
:)

From: postgis-users-boun...@lists.osgeo.org 
[postgis-users-boun...@lists.osgeo.org] on behalf of George Silva 
[georger.si...@gmail.com]
Sent: Tuesday, December 02, 2014 9:57 PM
To: PostGIS Users Discussion
Subject: Re: [postgis-users] Export from postGIS to ESRI Personal GeoDatabase

By links you mean...Foreign Keys? I don't think relationships are supported. If 
you have access to some ESRI libraries, you can create the geodatabase and 
throw together an ArcPy script to create the relationships back together.

If by links you mean textual links to files or urls, yes they will be 
supported. You will need to configure the hotlink feature inside arcgis.

You broke up the structure inside the PostGIS database. Create a view to put it 
all back together, and export the view.

I'm not sure why your users insist with Personal Geodatabase. There's only one 
use case for it: use it along with access to edit some records faster. If all 
they use is ArcGIS, it won't matter to them. They'll be able to do all the same 
things.

Check out that gist again, I've added the table definition. My use case is 
similar. I have a whole different structure here, with proper foreign keys, 
etc, and views that concatenate all the useful info for end-users and I export 
those.

For reference:

/*
Infraestrutura geral.
*/

CREATE SCHEMA temporario;

CREATE TABLE camada (

id SERIAL PRIMARY KEY,
esquema varchar(256) NOT NULL DEFAULT 'public',
nome_tabela varchar(256) NOT NULL,
nome varchar(256) NOT NULL,
descricao varchar(512),
exportar BOOLEAN DEFAULT TRUE,
nome_view varchar(512) NULL
);

esquema = schema;
nome_tabela = table_name;
nome = name (textual description);
descricao = description;
exportar = export, true or false (the function uses this for reference);
nome_view = view name, name of the view that holds the data for layer xyz

In a certain case, I had:

CREATE TABLE landuse (
id SERIAL PRIMARY KEY,
landuse INTEGER REFERENCES landuse_code(id)
geometry Polygon(4326));

CREATE TABLE landuse_code (
id SERIAL PRIMARY KEY,
landuse VARCHAR(256));

CREATE VIEW vw_landuse AS
SELECT
l.idhttp://l.id,
code.landuse,
l.geometry
FROM
landuse l
LEFT JOIN landuse_code code ON (l.landuse = code.idhttp://code.id);

Them I inserted that into my layer table, specifying the view and the table 
info (the view_name column expects this to be fully qualified, if you are 
placing your views in another schema, it should be schema.view) and running 
that script to create the File Geodatabase.

Cheers

On Tue, Dec 2, 2014 at 11:49 PM, Roxanne Reid-Bennett 
r...@tara-lu.commailto:r...@tara-lu.com wrote:
On 12/2/2014 1:37 PM, Robert Burgholzer wrote:
Wondering if anyone has any experience running from PG to ESRI mdb.  Basically, 
we want to use PostGIS as our master, and then replicate in GeoDB format for 
field personel to reference when they are unable to connect to the Web due to 
remote location (which happens very frequently in our line of work).

Any thoughts, experiences would be welcome.

I can't speak to ESRI mdb.

But.. Our mobile developer used an old (now unsupported) package [I don't know 
the name] with some tweaks to add LibGEOS support to SQLLite.  If you want to 
know more about that port - I can certainly make inquiries.  Our Web SaaS 
system runs on PG and the mobile app is connecting to that to pull data down to 
be available offline, which sounds a lot like your architectural needs.

Roxanne

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

--
George R. C. Silva
SIGMA Consultoria

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

Re: [postgis-users] Export from postGIS to ESRI Personal GeoDatabase

2014-12-03 Thread Burgholzer, Robert (DEQ)

Thanks Remi - Qgis may be a viable option for us... if I can sell it to our 
users :)


From: postgis-users-boun...@lists.osgeo.org 
[postgis-users-boun...@lists.osgeo.org] on behalf of Rémi Cura 
[remi.c...@gmail.com]
Sent: Wednesday, December 03, 2014 12:23 PM
To: PostGIS Users Discussion
Subject: Re: [postgis-users] Export from postGIS to ESRI Personal GeoDatabase

I think this is there:
http://www.oslandia.com/qgis-versioning-plugin-en.html

Cheers,
Rémi-C

2014-12-03 18:09 GMT+01:00 Paolo Cavallini 
cavall...@faunalia.itmailto:cavall...@faunalia.it:
-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

Il 03/12/2014 12:37, Rémi Cura ha scritto:
 Hey, it is not mdb, but I know some people from Oslandia have
 successfully developed a postgis -SQLite system with sync so to be
 able to work in the field. Maybe some stuff to re-use there (open
 source of course)

Hi Rémi,
do you have a link to it?
Merci.

- --
Paolo Cavallini - www.faunalia.euhttp://www.faunalia.eu
QGIS  PostGIS courses: http://www.faunalia.eu/training.html
-BEGIN PGP SIGNATURE-
Version: GnuPG v1

iEYEARECAAYFAlR/Q+UACgkQ/NedwLUzIr4byACfdjulKaH5qHTNkHWzBmJcsddD
XlEAoIBd3FiffNx8bPF13dNexJcq5udj
=CwiT
-END PGP SIGNATURE-
___
postgis-users mailing list
postgis-users@lists.osgeo.orgmailto: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] Spatially ordering data

Re: [postgis-users] Postgres as cache and renderer for vector tile server

Re: [postgis-users] PostGIS over distributed worksites

Re: [postgis-users] LineString within Polygon

Re: [postgis-users] Export from postGIS to ESRI Personal GeoDatabase

Re: [postgis-users] Export from postGIS to ESRI Personal GeoDatabase

6 matches

Site Navigation

Mail list logo

Footer information