Re: [postgis-users] PostGIS case usages

[Andy Colson]

On 10/26/18 12:05 PM, Regina Obe wrote:

Hey all.  So we've been in talks with our editor about having a 3rd Edition
of PostGIS hopefully to be released around the same time as PostGIS 3.0.

  
That said, if people can respond with what things they are currently using

PostGIS for and also what hosting they are using for PostGIS, that would be
helpful for us to get a better idea of focus points.

It'd be great if you posted on the list, but if you are shy or need your
usage anonymized, you can write directly to me.

Thanks,
Regina




To add to my write up here: https://postgis.net/2016/03/17/vanguard_appraisals/

Sometimes we get data w/out measurements, so when we are routing and lot sizing 
a map, sometimes I've gone low level to add measurements. (I break up each line 
of a parcel, measure it, and add an annotation.  All in sql, thousands of 
parcels all at once.)

The gis parcel data for a lot/parcel may not be correct when we get it, but 
once measured we can compare the gis data to the parcel data to know if we 
should remeasure when we visit the property.  It gives us a starting point, and 
helps organize and prepare the data.

I think the biggest thing is the easy of joining other data to the gis maps.  
Wanna combine the gis data with parcel data and show res acres vs agland acres. 
 Easy.  Color the map with parcel zoning?  Tax districts?  Easy and Easy.

I have not used esri software very much, so maybe I'm wrong about it, but it 
seems painful to join maps and databases.  We've had to add view's on top of 
the database just so esri could work with it.  There are some file formats it 
works ok with, and some that are very painful.

Not PostGIS, anything your twisted mind can work up, it'll do.  I'd bet I can 
write a perl stored proc that'll download a webpage, joins that to a redis fdw, 
join that to a csv, join that to active directory, and join that to Tiger data.

Another thing we do on the maps is find all parcels 100 feet from this point.  
Or 200 feet from this parcel.

We also do heat maps for land pricing.  Plot all the land rates for an area or 
subdivision to make sure nobody is too different.  Similar pricing of similar land is 
important.  Similar area <> similar land, but its a good start.

Exporting data is popular.  Maybe someone needs a csv dump of: parcelNumber, 
Address, Latitude, Longitude
We get data in different projections, but its easy to 
ST_Transform(ST_X(ST_Center(the_geom)))

Oh, tiger data.  We use that to supplement missing info.


We don't use cloud hosting.  We buy a commercial line and host from our office.

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

Re: [postgis-users] Optimizing PostGIS/Geoserver schema for huge dataset

[Andy Colson]

On 04/03/2017 04:39 PM, Andrew Gaydos wrote:

Hi,

Yes, in this case it only returns 9 rows, because my bounding box is
small.


Ah, that's the thing that cuts down rows the fastest, that should be the thing 
you query on first, then from the subset pull out streamflow and timeid.  Dunno 
if its possible to structure the data that way though.

 

I am getting 4x4=16 tiles - the requests are done in parallel so it's
not a straight 3x16 seconds calculation.


Ah, parallel, yes, that would answer that question.



Ah yes, duplicate indexes! Thanks for catching that! Would that incur
a performance hit on database reads?


No, it would slow down insert/delete though.

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

Re: [postgis-users] Optimizing PostGIS/Geoserver schema for huge dataset

[Andy Colson]

On 03/31/2017 09:33 PM, Andy Colson wrote:


This says 1000 is too many:
http://dba.stackexchange.com/questions/95977/maximum-partitioning-postgresql


Drat.  That says for 400K records you dont need 1000 partitions.  Sorry, I 
did't read that well, ignore this one.

I found another bit of interesting reading for partitions:
http://www.postgresql-archive.org/What-s-a-reasonable-maximum-number-for-table-partitions-td5837767.html

-Andy

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

Re: [postgis-users] Optimizing PostGIS/Geoserver schema for huge dataset

[Andy Colson]

This says 100:
http://stackoverflow.com/questions/6104774/how-many-table-partitions-is-too-many-in-postgres

This says 1000 is too many:
http://dba.stackexchange.com/questions/95977/maximum-partitioning-postgresql

Honestly you'd have to benchmark it, because I have no idea if there is a 
difference between 100 and 1000.

That being said, I'm surprised a good index isn't fast enough.  Partitions do 
cut the index size down, which is good, but it still has to scan all the child 
tables to see which match.

Do you ever update or delete data from the flow table?
Correct me if I'm wrong, but it looks like your where clause only uses fields: 
timeid, wkb_geometry and streamflow.  Yes?


Your explain analyze include table conus_flow, which isnt int your query or 
view.  Are you sure that's the right explain analyze?

-Andy


On 03/31/2017 08:18 PM, Andrew Gaydos wrote:

Thanks for the help!

I originally tried putting everything into a single non-partitioned table but 
the performance was horrible! Since each set of 2.3M rows shares the same 
timestamp, I thought this would be a good way to divide up the data when 
partitioning - I set a constraint on each, e.g.

table 1: constraint: timeid=101
table 2: constraint: timeid=102
etc.

I could try grouping times into a single table, e.g.

table 1: constraint: 100 <= timeid < 110
table 2: constraint: 110 <= timeid < 120
etc.

so that would give me 1000 partitions of 24 million rows each.

Is this what you were suggesting? What do you think the optimal balance of 
partitions and rows would be? 100 partitions of 240 million rows each? 10 
partitions of 2.4 billion rows each? At some point I think I would run into the 
insufferable performance I was getting with a single table, though.

Actually, now that I check the number of partitions is closer to 17,000, and 
number of rows per is 2.7M, so 46 billion rows altogether...

Thanks again!
-Andy

On Fri, Mar 31, 2017 at 6:15 PM, Andy Colson <a...@squeakycode.net 
<mailto:a...@squeakycode.net>> wrote:

On 03/31/2017 11:38 AM, Andrew Gaydos wrote:

Hi,



My questions are

 1. It seems that for every session, there is a one-time penalty for 
the first query (several minutes) after which queries tend to run much quicker 
(about 10 seconds for all the tiles to be served). What is going on here?
 2. Is there a way to optimize GeoServer's queries against this schema, 
or a more efficient query to try?
 3. other postgres optimizations that might help?

I'm pretty new to both GeoServer and PostGIS and have a sinking feeling 
that I could be structuring this dataset and queries more efficiently, but I've 
run out of ideas and don't have any postgres experts at work to ask, so I'm 
posting here.

Thanks for any insight!

-Andy


Andy's Unite!

err.. anyway, Here is the problem:

data table: (10,000 partitions, each with 2.3 million rows)



Lots of partitions will kill planning time. Look at the very bottom of:
https://www.postgresql.org/docs/9.5/static/ddl-partitioning.html 
<https://www.postgresql.org/docs/9.5/static/ddl-partitioning.html>

Do you have your heart set on lots of partitions?  How'd you feel about 
100? or maybe 1000?

-Andy



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

Re: [postgis-users] Optimizing PostGIS/Geoserver schema for huge dataset

[Andy Colson]

On 03/31/2017 11:38 AM, Andrew Gaydos wrote:

Hi,



My questions are

 1. It seems that for every session, there is a one-time penalty for the first 
query (several minutes) after which queries tend to run much quicker (about 10 
seconds for all the tiles to be served). What is going on here?
 2. Is there a way to optimize GeoServer's queries against this schema, or a 
more efficient query to try?
 3. other postgres optimizations that might help?

I'm pretty new to both GeoServer and PostGIS and have a sinking feeling that I 
could be structuring this dataset and queries more efficiently, but I've run 
out of ideas and don't have any postgres experts at work to ask, so I'm posting 
here.

Thanks for any insight!

-Andy


Andy's Unite!

err.. anyway, Here is the problem:


data table: (10,000 partitions, each with 2.3 million rows)



Lots of partitions will kill planning time. Look at the very bottom of:
https://www.postgresql.org/docs/9.5/static/ddl-partitioning.html

Do you have your heart set on lots of partitions?  How'd you feel about 100? or 
maybe 1000?

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

Re: [postgis-users] Bounding Box form center using lat long and meters.

[Andy Colson]

On 11/27/2016 09:09 AM, Chris wrote:

Yes, I'm not sure if I need to be true north all the times or not,
since this is a image i'm overlaying is there a way to calculate the
heading into the sql query so if its 12 degrees heading from north its
accounted for?
or should I shoot all my photos at 0 degrees north.

On Sun, Nov 27, 2016 at 8:46 AM, Andy Colson <a...@squeakycode.net> wrote:

On 11/26/2016 09:00 AM, Chris wrote:


I'm wanting to be able to generate the bounding cords from the data, I've
tried to figure out the query but haven't had much luck using PostGIS.
The center coordinates of the image is 35.1495343, -90.0490665 lat long
The box should be 180 Meters wide and 135 Meters high my Heading is 12
Degrees.
Image width and height are as follows if its needed 4000 W, 3000 H. If
something else would be more suitable like gdal please let me know.
I want to "automate" the geo-referencing on these single images with out
using something like QGIS" where I can use it in mapbox.

|"overlay": { "type": "image", "url":
"https://www.mapbox.com/mapbox-gl-js/assets/radar.gif;, "coordinates": [
[-80.425, 46.437], [-71.516, 46.437], [-71.516, 37.936], [-80.425, 37.936] ]
}|




So you are looking for some sql to generate a 180x135 box around a give
center point?

-Andy

___
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



As long as your happy with it being extended from the center point, I think a 
combination of http://postgis.net/docs/ST_Extent.html and 
http://postgis.net/docs/ST_Rotate.html should get you what you want.

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

Re: [postgis-users] Bounding Box form center using lat long and meters.

[Andy Colson]

On 11/26/2016 09:00 AM, Chris wrote:

I'm wanting to be able to generate the bounding cords from the data, I've tried 
to figure out the query but haven't had much luck using PostGIS.
The center coordinates of the image is 35.1495343, -90.0490665 lat long The box 
should be 180 Meters wide and 135 Meters high my Heading is 12 Degrees.
Image width and height are as follows if its needed 4000 W, 3000 H. If 
something else would be more suitable like gdal please let me know.
I want to "automate" the geo-referencing on these single images with out using 
something like QGIS" where I can use it in mapbox.

|"overlay": { "type": "image", "url": 
"https://www.mapbox.com/mapbox-gl-js/assets/radar.gif;, "coordinates": [ [-80.425, 46.437], [-71.516, 46.437], 
[-71.516, 37.936], [-80.425, 37.936] ] }|




So you are looking for some sql to generate a 180x135 box around a give center 
point?

-Andy

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

Re: [postgis-users] issue loading shapefiles into postgis via batch

[Andy Colson]

The other way is the pgpass file:

https://wiki.postgresql.org/wiki/Pgpass

-Andy


On 8/26/2016 12:27 PM, Bo Victor Thomsen wrote:

There are several ways to do this. One of the easiest (and most
insecure) is to set the PGPASSWORD environment variable with the
password for "postgres" user in the script before the psql command.


Regards

Bo Victor Thomsen

AestasGIS

Denmark


Den 26/08/16 kl. 15:59 skrev Mark Volz:


Hello,



I am having issues with a Windows batch load script.  This script
should scan for any new shapefiles in the “PostGIS Incoming” folder,
load the shapefiles into postGIS, then place the shapefiles in a
completed folder.  This script seemed to work in the past, but is now
complaining about a password – which I did not change.



What is wrong with this script, and or what can I do to specify a
password?



###

#  LOAD SHAPEFILES INTO POSTGIS SCRIPT

###



set PATH=C:\Program Files\PostgreSQL\9.2\bin;%PATH%

cd /d "D:\PostGISIncoming\LyonGIS"

rem load data to postgis

for %%f in (*.shp) do shp2pgsql -d -I -s 103749 -W LATIN1 %%f %%~nf |
psql -w -U postgres -d lyongis

rem move data to done folder

for %%a in (*.*) do move "%%a" "D:\PostGISIncoming\LyonGIS\Done"







Thanks

Sincerely,

*Mark Volz, GISP***





___
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



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

Re: [postgis-users] Help with PostGIS upgrade

[Andy Colson]

Not sure how I can.  You don't give any info.  What OS are you on?  What have you tried?  
What directions are you following?  You say you installed PostGis 2.2.0 ... how?  You say 
you "tried upgrade" ... how exactly?  Why are you going to 2.2.0 when 2.2.2 is 
out?

Did you google the error message: "postgis.backend' is already set and cannot be 
changed until you reconnect".  Because the very first hit is a trac ticket about it. 
 Did you read that?

-Andy


On 07/28/2016 03:29 PM, Karthick Thanigaimani wrote:

Hi All,

could anyone please help on this.

thank you.

regards,
karthick

--
*From:* Karthick Thanigaimani <kargold...@yahoo.com>
*To:* PostGIS Users Discussion <postgis-users@lists.osgeo.org>
*Sent:* Wednesday, 27 July 2016 4:23 PM
*Subject:* Re: [postgis-users] Help with PostGIS upgrade

hi Andy / All,

Thanks for the help I installed postGIS 2.2.0 in the Postgresql 9.3.8 and tried 
upgrade.
It just waits for ages and nothing happens except for the below message.

alter extension postgis update to '2.2.0';
WARNING:  'postgis.backend' is already set and cannot be changed until you 
reconnect
CONTEXT:  SQL statement "SELECT  postgis_lib_version()"
PL/pgSQL function postgis_major_version_check() line 21 at SQL statement


is it because of any issues with libraries or a bug?

Regards,
Karthick

--
*From:* Pierre Bails <ba...@getredbird.com>
*To:* PostGIS Users Discussion <postgis-users@lists.osgeo.org>
*Sent:* Tuesday, 26 July 2016 6:05 PM
*Subject:* Re: [postgis-users] Help with PostGIS upgrade

Hi,

This works for us on Debian Jessie. It should also work on Ubuntu.

# install laster version of postgres & postgis
$ sudo apt-get install postgresql-9.5 postgresql-9.5-postgis-2.2 
postgresql-9.5-postgis-2.2-scripts postgresql-client-9.5 postgresql-contrib-9.5

# stop and drop the cluster 9.5 (if exists)
$ sudo pg_dropcluster 9.5 main --stop

# Upgrade the 9.3 cluster to the latest version. (very long, depending of 
datasize)
$ sudo pg_upgradecluster 9.3 main

# check the cluster list
$ pg_lsclusters
Ver Cluster Port Status OwnerData directory   Log file
9.3 main5433 down   postgres /var/lib/postgresql/9.3/main 
/var/log/postgresql/postgresql-9.3-main.log
9.5 main5432 online postgres /var/lib/postgresql/9.5/main 
/var/log/postgresql/postgresql-9.5-main.log

# after works fine, drop the old cluster
$ sudo pg_dropcluster 9.3 main

# then, remove old packages

Good luke !

Pierre


Le lun. 25 juil. 2016 à 23:37, Andy Colson <a...@squeakycode.net 
<mailto:a...@squeakycode.net>> a écrit :

On 7/25/2016 3:49 PM, Karthick Thanigaimani wrote:
> Hi,
>
>
>
> We have a database that is of ~1 TB and the schemas are big as well. We
> would like to upgrade the database version from PostgreSQL 9.3.x to
> 9.5.x and the DB has PostGIS 2.1.2 which also need to be updated to
> 2.2.2 as per version 9.5.x.
&g

Re: [postgis-users] Help with PostGIS upgrade

[Andy Colson]

On 7/25/2016 3:49 PM, Karthick Thanigaimani wrote:

Hi,



We have a database that is of ~1 TB and the schemas are big as well. We
would like to upgrade the database version from PostgreSQL 9.3.x to
9.5.x and the DB has PostGIS 2.1.2 which also need to be updated to
2.2.2 as per version 9.5.x.
I installed the postGIS 2.2.2 libraries and tried updating the version
2.2.2 but it says there is no upgrade path.
and when we run the pg_upgrade compatibility check it says postGIS has
to be upgraded.


Is there anyway to achieve the upgrade without doing pg_dump of the
schemas.?

Please can anyone help.

thank you.

Regards,
Karthick


I just did this actually.  I first upgraded my PG9.3 to Postgis 2.2.2, 
then I used pg_upgrade to go from 9.3 to 9.5, which worked fine.


Sounds like you had problems in PG9.3 upgrading Postgis.  That should 
work ok.


Really, though, if should not matter which you upgrade first.  Can you 
install PostGIS 2.1.2 into PG 9.5?  If so, do that and upgrade PG first. 
 It should work ok.


-Andy

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

Re: [postgis-users] Encountering error while installing postgis from source?

[Andy Colson]

On 06/27/2016 11:55 AM, sablok wrote:

I am using the following command to make and make install postgis-2.2.2 from
source and I have all the other dependencies configured properly -

export POSTGIS_BUILD_VERSION=2.2.2
cd ${postgis_src_path}/configuration/postgis/postgis-$POSTGIS_BUILD_VERSION
&& ./configure --with-pgconfig=${build_root}/bin/pg_config
--with-jsondir=${build_root} --with-projdir=${build_root}
--with-geosconfig=${build_root}/bin/geos-config
--with-gdalconfig=${build_root}/bin/gdal-config
cd ${postgis_src_path}/configuration/postgis/postgis-$POSTGIS_BUILD_VERSION
&& make -j 4
cd ${postgis_src_path}/configuration/postgis/postgis-$POSTGIS_BUILD_VERSION
&& make clean
cd ${postgis_src_path}/configuration/postgis/postgis-$POSTGIS_BUILD_VERSION
&& make install
cd
${postgis_src_path}/configuration/postgis/postgis-$POSTGIS_BUILD_VERSION/extensions
&& make
cd
${postgis_src_path}/configuration/postgis/postgis-$POSTGIS_BUILD_VERSION/extensions
&& make install

After the above commands execute the configure step terminates gracefully
with all the configuration files set up properly and I can see the following
on my screen

PostGIS is now configured for x86_64-unknown-linux-gnu

The make process however fails with make: *** [all] Error 1  and I see the
following message on my screen

lwout_wkb.c: In function ‘empty_to_wkb_buf’:
lwout_wkb.c:319:22: error: ‘NAN’ undeclared (first use in this function)
   static double nn = NAN;
  ^
lwout_wkb.c:319:22: note: each undeclared identifier is reported only once
for each function it appears in
make[1]: *** [lwout_wkb.lo] Error 1
make[1]: *** Waiting for unfinished jobs



does anyone have any idea as to why is this happening, I searched about it
and it seems to be related to GCC erroring out. I am not sure as this is my
first experience with building postgis. Thanks in advance.



Did you get an error/warning about not finding include files?

http://postgis.net/docs/doxygen/2.2/d0/d7a/lwgeodetic_8h.html seems to define 
NAN.  You don't happen to have multiple versions of the header files 
(especially lwgeodetic.h) do you?

-Andy

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

Re: [postgis-users] Call for Case Studies and knowledge about who uses PostGIS and how

[Andy Colson]

On 3/9/2016 11:49 AM, Nicklas Avén wrote:

Hi all

At PostGIS web site http://www.postgis.net, there is a case study list
of PostGIS usage. The list is very short and not touched for some years.



Here is a first draft, spelling/fixes/suggestions welcome:


Vanguard Appraisals is new to the GIS world. In fact, we aren't really 
in the GIS world; we just kind of brush up against it.  We do mass 
property appraisal for entire county and city jurisdictions, and we 
develop software to collect, price and maintain values.  We also host 
assessment data online so that homeowners can search and find property 
information much simpler from the comfort of their own home.  Our 
software and websites are used in 7 states (IA, IL, MN, MO, NE, ND, SD).


We were happy in our little world, doing parcel stuff, not really 
knowing about or using GIS, but then the landscape started changing, and 
GIS started popping up all over the place.  Our clients starting asking 
us if we could host their GIS data, as well as their parcel data.  Some 
of our clients are very small; there is one person in the Assessor's 
office with one computer, no server, and a very small internet pipe. 
Some of our clients are big with many users, multiple servers, and an 
internet pipe that makes me blush. :-)


We searched and found something that already worked with our favorite 
database: PostgreSQL.  PG is already hosting our parcel data, so it 
seemed like a good idea to let it host our GIS data too. Using PostGIS 
combined with MapServer, Perl and OpenLayers, we came up with online 
maps that fit the bill:

 1) Great performance.
 2) Sql:  the ability to write sql to join our existing parcel data 
with GIS data makes it simple to work with, powerful, and fast.
 3) Free:  because we didn't pay for anything, we didn't charge 
anything.  Government Assessor’s offices don't have to charge tax payers 
to get their GIS online.


PostGIS has been a great decision.  When one of our programmers came up 
with a crazy idea about doing a sales ratio analysis and highlighting 
all the properties on the map, not only was it possible but not that 
hard to do, and it has already been implemented because of PostGIS.


I also cannot stress enough how good and helpful the online community 
has been.  I went from knowing nothing about GIS to hosting maps only 
because of them and all the questions they helped with over the years.


Vanguard Appraisals:  http://camavision.com/
Assessment Data:  http://iowaassessors.com/

We host parcel data for all the yellow links, but we don't host the maps 
for all of them.  Some counties we host maps for are: Washington MN, 
Jasper IA, Van Buren IA, Iowa City IA.  Just over 50 counties so far.


You can find the map embedded into the parcel page:
http://vanburen.iowaassessors.com/parcel.php?parcel=000600307304130

or Full Page:
http://maps.camavision.com/map/jasperia

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

Re: [postgis-users] prj2esri.org

[Andy Colson]

On 1/12/2016 3:17 AM, Andrea Aime wrote:

On Tue, Jan 12, 2016 at 3:52 AM, Clifford Snow > wrote:

I'm looking for code to suggest to QGIS developers to use. Right now
QGIS doesn't recognize Washington State Plane North/South. And
besides, I'm curious how it's accomplished. Doing it by hand
resulted in me picking the wrong match!


I wrote the first version of site several years ago, it's a simple
application of the GeoTools referencing subsystem, backed by the EPSG
database.
Not sure how it works today, but I can tell you how it used to work back
then.

When you paste a PRJ, first it parses it and tries to perform a match
against the official EPSG database, by first making an indexed
search by projection name and a few other params, falling on a brute
force scan and compare if the first does not match.
If not even that works, then it has a secondary Lucene index with all
projections WKTs stored inside, and it will do a
text based search against it, hoping to find something similar (aka
"shot in the dark").

Hope this helps

Cheers
Andrea



This:
http://www.gdal.org/classOGRSpatialReference.html#af1a29550373c2c14bd11514641e58e7c

converts from proj4, perhaps the code could be somehow reversed?

-Andy

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

Re: [postgis-users] Queries on partitioned table not behaving as expected

[Andy Colson]

Yep, +1 do all this.

Additionally: select performance is not a reason to use partitioned 
tables.  Its only update/delete performance that's increased.


Ok, _some_ selects might be faster with partitions, but I'd bet not 
indexed select statements.  You'd have to benchmark it.


If you really want 1400+ tables you might be able to write yourself a 
function that figures out what table/tables to query and generates a sql 
statement dynamically.


Like from the docs:

http://www.postgresql.org/docs/9.4/static/plpgsql-statements.html#PLPGSQL-STATEMENTS-EXECUTING-DYN

(sorry, watch the word wrap)

-Andy


On 11/2/2015 12:53 PM, Nicolas Ribot wrote:

Hi,

AFAIU, the restriction on partitioned tables is even bigger. From PG doc
(http://www.postgresql.org/docs/current/interactive/ddl-partitioning.html#DDL-PARTITIONING-CONSTRAINT-EXCLUSION)
:

"The following caveats apply to constraint exclusion:

  *

Constraint exclusion only works when the query's WHERE clause
contains constants (or externally supplied parameters). For example,
a comparison against a non-immutable function such
as|CURRENT_TIMESTAMP| cannot be optimized, since the planner cannot
know which partition the function value might fall into at run time.

"
Using a subquery to provide values won't trigger the constraint exclusion.
One solution could be an anonymous block or a function to explicitly
write query with constant values got from a previous query.

Rubio, take also care that a lot of partitioned tables is not
recommended by Pg doc:
"All constraints on all partitions of the master table are examined
during constraint exclusion, so large numbers of partitions are likely
to increase query planning time considerably. Partitioning using these
techniques will work well with up to perhaps a hundred partitions; don't
try to use many thousands of partitions."

I would go for a smaller set of bigger tables (10's millions rows or so).

Nicolas

On 2 November 2015 at 18:28, Bborie Park > wrote:

That check constraint is not going to help you as it is too
complicated for the partitioning task. I don't believe the
partitioning constraint can be functional but needs to be simpler
and built upon a basic data type...

Something like...

CREATE TABLE max_extent AS (
   id integer PRIMARY KEY,
   geom geometry(POLYGON)
);

And then the parent/partition tables had a reference to pc_extent...

CREATE TABLE ahn3_pointcloud.ahn3_all
(
   id integer NOT NULL,
   pa pcpatch(7),
   max_extent_id integer
);

Then your queries become...

WITH max_extents AS (
SELECT
   id
FROM max_extent
WHERE PC_Intersects(geom,
ST_MakeEnvelope(120740,486076,121074,486292, 28992))
)
SELECT
*
FROM ahn3_all
JOIN max_extents
   ON ahn3_all.max_extent_id = max_extents.id 

-bborie

On Mon, Nov 2, 2015 at 8:45 AM, Rubio Vaughan
> wrote:

Dear all,

We are trying to load a large LIDAR pointcloud dataset into
multiple tables in PostGIS, using the point cloud extension.
Eventually the whole data set will consist of 1400+ tables,
containing about 1 million records each. Using a union view on
all these tables would result in terrible performance, which is
why we are trying to optimize query performance using
partitioned tables. According to the documentation, the use of
partitioned tables with CHECK constraints should cause the query
planner to only scan those tables for which the CHECK constraint
matches. Excerpt from the documentation:


*18.7.4. Other Planner Options*
constraint_exclusion(enum): Controls the query planner's use of
table constraints to optimize queries. The allowed values of
constraint_exclusion are on (examine constraints for all
tables), off (never examine constraints), and partition (examine
constraints only for inheritance child tables and UNION ALL
subqueries). partition is the default setting. It is often used
with inheritance and partitioned tables to improve performance.
When this parameter allows it for a particular table, the
planner compares query conditions with the table's CHECK
constraints, and omits scanning tables for which the conditions
contradict the constraints.



However, as you can see from the example query below, the
indexes for all child tables are still scanned. I would expect
the query planner to only scan table c_25gn1, which contains the
queried region. Does anyone here have experience 

Re: [postgis-users] GDAL Netcdf point data to PostGIS?

[Andy Colson]

On 08/03/2015 01:27 PM, Gerry Creager - NOAA Affiliate wrote:

I might be posting to the wrong forum but... would this work? We aggregate 
meteorological surface data in netcdf form, and I'd like to populate a database 
with it..
Thanks
gerry
--
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)



Is it gis data (latitude/longitude type stuff)?  Or is it something else (like 
temperature)?

How would you like to use it in Postgres?  What kind of sum()'s and where's are 
you going to write?

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

Re: [postgis-users] Find unique date and code values before applying st_intersects

[Andy Colson]

Date: Thu, 11 Jun 2015 16:53:46 -0500
From: a...@squeakycode.net
To: postgis-users@lists.osgeo.org
Subject: Re: [postgis-users] Find unique date and code values before applying 
st_intersects

On 6/11/2015 3:36 PM, Gery . wrote:

Hello,

I have two tables, one containing polygons and one with points:

# polygons
gid | integer | not null default
nextval('cuadro_produce_region_gid_seq'::regclass)
id | character varying(10) |
zona | character varying(10) |
area | character varying(10) |
cod | double precision |
seccion | double precision |
ordxdistc | double precision |
geom | geometry(PolygonZM,4326) |

# points
nombre_embarcacion | character varying(150) |
matricula_embarcacion | character varying(150) |
tipo_casco | character varying(150) |
capacidad_bodega | character varying(150) |
nombre_aparejo | character varying(150) |
descripcion_proveedor | character varying(150) |
fecha | character varying(150) |
longitud | numeric |
latitud | numeric |
velocidad | numeric |
rumbo | numeric |
ts | tsvector |
geom | geometry(Point,4326) |

Finding all points, first filtered by velocity (velocidad field),
inside each region was quite easy:

select
cuadro_produce_region.gid,cuadro_produce_region.area,cuadro_produce_region.zona,cuadro_produce_region.cod,count(*)
as Nro from temporada1erapesca2012_point_wgs84,cuadro_produce_region
where (temporada1erapesca2012_point_wgs84.velocidad between '0.2' and
'1.6') and
st_intersects(cuadro_produce_region.geom,temporada1erapesca2012_point_wgs84.geom)
group by cuadro_produce_region.gid order by
cuadro_produce_region.gid,cuadro_produce_region.cod;

but now I need to select unique values from fields fecha and
matricula_embarcacion of the table
'temporada1erapesca2012_point_wgs84' first and then apply the
intersection with the 'cuadro_produce_region' table, I tried distinct
in the where clause with no success so far:

select
cuadro_produce_region.gid,cuadro_produce_region.area,cuadro_produce_region.zona,cuadro_produce_region.cod,count(*)
as Nro from temporada1erapesca2012_point_wgs84,cuadro_produce_region
where (temporada1erapesca2012_point_wgs84.velocidad between '0.2' and
'1.6' and
distinct(temporada1erapesca2012_point_wgs84.matricula_embarcacion) and
distinct(temporada1erapesca2012_point_wgs84.fecha)) and
st_intersects(cuadro_produce_region.geom,temporada1erapesca2012_point_wgs84.geom)
group by cuadro_produce_region.gid order by
cuadro_produce_region.gid,cuadro_produce_region.cod;

how could I do this query? fecha field has this structure: 16/06/2012
03:00

Any hints are welcomed, thanks in advance,




Your first email had much better formatting.



but now I need to select unique values from fields fecha and
matricula_embarcacion of the table
'temporada1erapesca2012_point_wgs84' first and then apply the
intersection with the 'cuadro_produce_region' table, I tried distinct
in the where clause with no success so far:


Start with the first thing, select it out, then wrap it into a derived
table.

so first:

select distinct fecha, matricula_embarcacion
from temporada1erapesca2012_point_wgs84

If that returns to you the right data, then expand it:

select whatever
from cuadro_produce_region
inner join (
select distinct fecha, matricula_embarcacion
from temporada1erapesca2012_point_wgs84
) as tmpPoints on (tmpPoints.fecha = cuadro_produce_region.fecha)


-Andy




On 6/12/2015 9:42 AM, Gery . wrote: Thanks a lot Andy, I just got right 
the first query:


 select distinct to_date(fecha,'DD/MM/YY') as 
fecha,matricula_embarcacion from temporada1erapesca2012_point_wgs84 
order by matricula_embarcacion;


 but the second one is not working:

 select 
cuadro_produce_region.gid,cuadro_produce_region.area,cuadro_produce_region.zona,cuadro_produce_region.cod,count(*) 
from cuadro_produce_region inner join (select distinct 
to_date(fecha,'DD/MM/YY'),matricula_embarcacion from 
temporada1erapesca2012_point_wgs84 order by matricula_embarcacion) as 
tmpPtos on (tmpPtos.fecha = cuadro_produce_region.fecha);ERROR:  column 
tmpptos.fecha does not exist
 LINE 1: ...84 order by matricula_embarcacion) as tmpPtos on 
(tmpPtos.fe...


 and this is because there is no fecha in cuadro_produce_region. 
INNER JOIN would work better than ST_intersects in this case? I mean, I 
need to find all points inside each region (10 regions) of the polygon 
table (ie. cuadro_produce_region), and st_intersects works well in the 
query below:


 select 
cuadro_produce_region.gid,cuadro_produce_region.area,cuadro_produce_region.zona,cuadro_produce_region.cod,count(*) 
as Nro from temporada1erapesca2012_point_wgs84,cuadro_produce_region 
where (temporada1erapesca2012_point_wgs84.velocidad between '0.2' and 
'1.6') and 
st_intersects(cuadro_produce_region.geom,temporada1erapesca2012_point_wgs84.geom) 
group by cuadro_produce_region.gid order by 
cuadro_produce_region.gid,cuadro_produce_region.cod;



 



Sorry, I didn't mean to confuse.  I'm not familiar with your tables, I 
had 

Re: [postgis-users] Find unique date and code values before applying st_intersects

[Andy Colson]

On 6/11/2015 3:36 PM, Gery . wrote:

Hello,

I have two tables, one containing polygons and one with points:

# polygons
gid   | integer  | not null default
nextval('cuadro_produce_region_gid_seq'::regclass)
id | character varying(10)|
zona| character varying(10)|
area | character varying(10)|
cod  | double precision |
seccion   | double precision |
ordxdistc | double precision |
geom   | geometry(PolygonZM,4326) |

# points
  nombre_embarcacion| character varying(150) |
  matricula_embarcacion | character varying(150) |
  tipo_casco| character varying(150) |
  capacidad_bodega  | character varying(150) |
  nombre_aparejo| character varying(150) |
  descripcion_proveedor | character varying(150) |
  fecha | character varying(150) |
  longitud  | numeric|
  latitud   | numeric|
  velocidad | numeric|
  rumbo | numeric|
  ts| tsvector   |
  geom  | geometry(Point,4326)   |

Finding all points, first filtered by velocity (velocidad field),
inside each region was quite easy:

select
cuadro_produce_region.gid,cuadro_produce_region.area,cuadro_produce_region.zona,cuadro_produce_region.cod,count(*)
as Nro from temporada1erapesca2012_point_wgs84,cuadro_produce_region
where (temporada1erapesca2012_point_wgs84.velocidad between '0.2' and
'1.6') and
st_intersects(cuadro_produce_region.geom,temporada1erapesca2012_point_wgs84.geom)
group by cuadro_produce_region.gid order by
cuadro_produce_region.gid,cuadro_produce_region.cod;

but now I need to select unique values from fields fecha and
matricula_embarcacion of the table
'temporada1erapesca2012_point_wgs84' first and then apply the
intersection with the 'cuadro_produce_region' table, I tried distinct
in the where clause with no success so far:

select
cuadro_produce_region.gid,cuadro_produce_region.area,cuadro_produce_region.zona,cuadro_produce_region.cod,count(*)
as Nro from temporada1erapesca2012_point_wgs84,cuadro_produce_region
where (temporada1erapesca2012_point_wgs84.velocidad between '0.2' and
'1.6' and
distinct(temporada1erapesca2012_point_wgs84.matricula_embarcacion) and
distinct(temporada1erapesca2012_point_wgs84.fecha)) and
st_intersects(cuadro_produce_region.geom,temporada1erapesca2012_point_wgs84.geom)
group by cuadro_produce_region.gid order by
cuadro_produce_region.gid,cuadro_produce_region.cod;

how could I do this query? fecha field has this structure:  16/06/2012
03:00

Any hints are welcomed, thanks in advance,




Your first email had much better formatting.


 but now I need to select unique values from fields fecha and
 matricula_embarcacion of the table
 'temporada1erapesca2012_point_wgs84' first and then apply the
 intersection with the 'cuadro_produce_region' table, I tried distinct
 in the where clause with no success so far:

Start with the first thing, select it out, then wrap it into a derived 
table.


so first:

select distinct fecha, matricula_embarcacion
from temporada1erapesca2012_point_wgs84

If that returns to you the right data, then expand it:

select whatever
from cuadro_produce_region
inner join (
select distinct fecha, matricula_embarcacion
from temporada1erapesca2012_point_wgs84
) as tmpPoints on (tmpPoints.fecha = cuadro_produce_region.fecha)


-Andy

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

Re: [postgis-users] Expand odd shapes

[Andy Colson]

Like this:

http://testmaps.camavision.com/map/cassmo?pin=08083430332000

The blue part is the selected parcel.  The gray is the ST_Buffer.

-Andy


On 6/10/2015 8:27 AM, Andy Colson wrote:

Because I actually need to see the shape.  One step is to see the shape,
the second will be to find the parcels that touch it, which will use
ST_DWithin for speed plus ST_Buffer for accuracy.

-Andy


On 6/10/2015 1:43 AM, Rémi Cura wrote:

Why not use ST_DWithin with the original triangle, it is the best option
regarding speed and precision.
Cheers
Remi-C

Le 10 juin 2015 01:30, Andy Colson a...@squeakycode.net
mailto:a...@squeakycode.net a écrit :

At first I thought no, because it creates a circle around a point:

Returns a geometry that represents all points whose distance from
this Geometry is less than or equal to distance

But now that I read it again, maybe it does do what I'd like.  If I
pass a rectangle, it'll return all points = radius.

I'll give it a shot and try it out.  Thanks for the help.

-Andy


On 06/09/2015 04:48 PM, Basques, Bob (CI-StPaul) wrote:

Can you use ST_Buffer??

bobb



On Jun 9, 2015, at 4:34 PM, Andy Colson
a...@squeakycode.net mailto:a...@squeakycode.net wrote:

Hi all.

I have buffer selection problem.  My site can drop a point,
then find all the parcels within 100 feet of that point.  No
problem.

One client however would like to select a parcel (maybe its
a square, maybe its a triangle, or any other odd shape), and
find all other parcels within 100 feet of any point within
the source parcel.

100 is optional.  They could choose 50, or 150.

I'm not sure how to do something like this.  I found
ST_Expand, but that'll create a bounding box around the
triangle.

ST_TransScale could scale it, but I'm not sure what the
scale is.  The user might type in 50 feet or 150 feet, I'm
not sure how to calculate a deltaX and deltaY scale for that.

Any hints on how I might go about this?  I'm a bit stuck.

Thanks,

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


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


___
postgis-users mailing list
postgis-users@lists.osgeo.org mailto: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


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

Re: [postgis-users] Expand odd shapes

[Andy Colson]

Huh.  My email from last night didnt make it though.

Sorry to spam the list.  This is my last one, I swear.

ST_Buffer was exactly what I was looking for.

Thanks Bob for pointing me back the right direction.

-Andy


On 6/10/2015 8:31 AM, Andy Colson wrote:

Like this:

http://testmaps.camavision.com/map/cassmo?pin=08083430332000

The blue part is the selected parcel.  The gray is the ST_Buffer.

-Andy


On 6/10/2015 8:27 AM, Andy Colson wrote:

Because I actually need to see the shape.  One step is to see the shape,
the second will be to find the parcels that touch it, which will use
ST_DWithin for speed plus ST_Buffer for accuracy.

-Andy


On 6/10/2015 1:43 AM, Rémi Cura wrote:

Why not use ST_DWithin with the original triangle, it is the best option
regarding speed and precision.
Cheers
Remi-C

Le 10 juin 2015 01:30, Andy Colson a...@squeakycode.net
mailto:a...@squeakycode.net a écrit :

At first I thought no, because it creates a circle around a point:

Returns a geometry that represents all points whose distance from
this Geometry is less than or equal to distance

But now that I read it again, maybe it does do what I'd like.  If I
pass a rectangle, it'll return all points = radius.

I'll give it a shot and try it out.  Thanks for the help.

-Andy


On 06/09/2015 04:48 PM, Basques, Bob (CI-StPaul) wrote:

Can you use ST_Buffer??

bobb



On Jun 9, 2015, at 4:34 PM, Andy Colson
a...@squeakycode.net mailto:a...@squeakycode.net wrote:

Hi all.

I have buffer selection problem.  My site can drop a point,
then find all the parcels within 100 feet of that point.  No
problem.

One client however would like to select a parcel (maybe its
a square, maybe its a triangle, or any other odd shape), and
find all other parcels within 100 feet of any point within
the source parcel.

100 is optional.  They could choose 50, or 150.

I'm not sure how to do something like this.  I found
ST_Expand, but that'll create a bounding box around the
triangle.

ST_TransScale could scale it, but I'm not sure what the
scale is.  The user might type in 50 feet or 150 feet, I'm
not sure how to calculate a deltaX and deltaY scale for
that.

Any hints on how I might go about this?  I'm a bit stuck.

Thanks,

-Andy


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

Re: [postgis-users] Expand odd shapes

[Andy Colson]

At first I thought no, because it creates a circle around a point:

Returns a geometry that represents all points whose distance from this Geometry is 
less than or equal to distance

But now that I read it again, maybe it does do what I'd like.  If I pass a 
rectangle, it'll return all points = radius.

I'll give it a shot and try it out.  Thanks for the help.

-Andy


On 06/09/2015 04:48 PM, Basques, Bob (CI-StPaul) wrote:

Can you use ST_Buffer??

bobb




On Jun 9, 2015, at 4:34 PM, Andy Colson a...@squeakycode.net wrote:

Hi all.

I have buffer selection problem.  My site can drop a point, then find all the 
parcels within 100 feet of that point.  No problem.

One client however would like to select a parcel (maybe its a square, maybe its 
a triangle, or any other odd shape), and find all other parcels within 100 feet 
of any point within the source parcel.

100 is optional.  They could choose 50, or 150.

I'm not sure how to do something like this.  I found ST_Expand, but that'll 
create a bounding box around the triangle.

ST_TransScale could scale it, but I'm not sure what the scale is.  The user 
might type in 50 feet or 150 feet, I'm not sure how to calculate a deltaX and 
deltaY scale for that.

Any hints on how I might go about this?  I'm a bit stuck.

Thanks,

-Andy
___
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

[postgis-users] Expand odd shapes

[Andy Colson]

Hi all.

I have buffer selection problem.  My site can drop a point, then find 
all the parcels within 100 feet of that point.  No problem.


One client however would like to select a parcel (maybe its a square, 
maybe its a triangle, or any other odd shape), and find all other 
parcels within 100 feet of any point within the source parcel.


100 is optional.  They could choose 50, or 150.

I'm not sure how to do something like this.  I found ST_Expand, but 
that'll create a bounding box around the triangle.


ST_TransScale could scale it, but I'm not sure what the scale is.  The 
user might type in 50 feet or 150 feet, I'm not sure how to calculate a 
deltaX and deltaY scale for that.


Any hints on how I might go about this?  I'm a bit stuck.

Thanks,

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

Re: [postgis-users] Strange Left Join behaviour

[Andy Colson]

On 12/29/2014 8:46 AM, Arnaud Vandecasteele wrote:

Hey all,

It's been a couple of days that I'm trying to compute the difference
(like the geoprocessing difference with QGIS) between two geometry
(polygon) tables. These tables (let's call them A and B) contain
polygons where :
  - polygon from A can be intersected by one or more polygon from B
  - polygon from A can have no spatial relationship with polygon from B
(no intersection)

To realize this difference I've joined the two tables with a LEFT OUTER
JOIN so even if I don't have any intersection between A and B I should
get the geometry from A.

Below it's an example of the SQL query :
*
SELECT  A.id,
 COALESCE(
 ST_Difference(
 A.the_geom,
 ST_Union(B.the_geom)
 ),
 A.the_geom
 ) As the_geom
FROM A
LEFT JOIN B ON A.the_geom  B.the_geom
AND ST_Intersects(A.the_geom, B.the_geom)
GROUP BY A.id;
*

The difference between objects from table A that are intersected by one
or more objects from B is correct. But I don't get the objects from A
that are not intersected by B. I don't understand why as I use a  LEFT
OUTER JOIN and the COALESCE function.

Could you please tell me what I'm doing wrong or what I don't understand ?

Thanks for your help.

Arnaud




I'm assuming ST_Intersects will return null if B.the_geom is null.  Try:

LEFT JOIN B ON A.the_geom  B.the_geom
AND (B.the_geom is null or ST_Intersects(A.the_geom, B.the_geom))

-Andy



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

Re: [postgis-users] Why won’t my SELECT query use the INDEX?

[Andy Colson]

On 12/16/2014 9:18 AM, Casper Børgesen (CABO) wrote:

This seems like one of the most asked questions in the PostgreSQL world,
but I guess I haven’t understood all the answers yet:

Why won’t my SELECT query use the INDEX I have created for it?

I have a table with about 18mio rows.

My SELECT statement looks like this:

SELECT id,encode(ST_AsEWKB(geom),'base64') as geometry

FROM my_table

WHERE geom 

ST_GeomFromText('POLYGON ((892267.1937422 6148208.34251139,
892500.497129949 6148208.34251139, 892500.497129949 6148009.40012468,
892267.1937422 6148009.40012468, 892267.1937422 6148208.34251139))',
ST_SRID(geom))

The EXPLAIN ANALYZE of the above statement returned this:

Seq Scan on my_table  (cost=0.00..4329124.83 rows=1731 width=1700)
(actual time=194785.745..1553525.244 rows=138 loops=1)

  Filter: (geom  st_geomfromtext('POLYGON ((892267.1937422
6148208.34251139, 892500.497129949 6148208.34251139, 892500.497129949
6148009.40012468, 892267.1937422 6148009.40012468, 892267.1937422
6148208.34251139))'::text, st_srid(geom)))

  Rows Removed by Filter: 17311187

Total runtime: 1553525.352 ms

The POLYGON described above is located at the outer edge of the 17mio
geometries and the extent is pretty small.

I have executed a VACUUM ANALYZE to clean up the statistics, which
didn’t seem to improve the results.

My INDEX has been created like this:

CREATE INDEX my_table_geom_idx

   ON my_table

   USING gist

   (geom);

Upon reading up on this issue I have changed the following in my
postgresql.conf:

random_page_cost = 2.0

shared_buffers = 512MB

work_mem = 8MB

maintenance_work_mem = 256MB

effective_cache_size = 8GB

The HARDWARE is a Windows 2012R2 with 12GB ram and SAS HDDs. PostgreSQL
9.3 x64 and PostGIS 2.1.1.

Can any ask me the right questions so I can solve my INDEX problem?



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




I doubt changing postgresql.conf options will have any affect.

Its the st_srid(geom) call that's a problem.

Remove it, or specify the integer value.  The function call messes it up.

This should work:


explain analyze
SELECT id,encode(ST_AsEWKB(geom),'base64') as geometry
FROM my_table
WHERE geom 
ST_GeomFromText('POLYGON ((892267.1937422 6148208.34251139, 
892500.497129949 6148208.34251139, 892500.497129949 6148009.40012468, 
892267.1937422 6148009.40012468, 892267.1937422 6148208.34251139))')



-Andy



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

Re: [postgis-users] FileGDB curves

[Andy Colson]

Mapserver displays it the same way:

http://testmaps.camavision.com/map/washingtonmn?pin=16.028.21.11.0039

The data looks like:

gis=# select st_AsText(the_geom) from streets where gid = 11517;
-[ RECORD 1 
]--
st_astext | MULTILINESTRING((-10344354.4038468 
5608573.22422463,-10344363.9860388 5608565.68874797,-10344398.9885982 
5608528.81799097,-10344438.5518419 5608468.68094317,-10344404.2129173 
5608265.5414558,-10344355.0291817 5608227.92219317,-10344239.0250123 
5608196.3261761,-10344230.7760714 5608197.59004929,-10344187.3835781 
5608212.14184435))




So I'm assuming that its a convert problem, not a display problem.

I'm not sure how to use ST_LineToCurve.  This is a road layer and 
everything is a MULTILINESTRING, I can't tell the straight from the 
curves.  Can I?


Thanks,

-Andy



On 11/12/2014 3:58 AM, Rémi Cura wrote:

Hey,
first qgis can't deal with curve (i think),
so your data may be curved in your postgis but the visualisation would
be incorrect
  so if you are going to see some curve in qgis, you have to convert the
curve to approximate theim using
http://postgis.net/docs/ST_CurveToLine.html,
then display the geom
(you can do this in a view if you don't want to duplicate data)

Now if you are really really sure that data in postgis is bad (use
ST_AsTExt(geom) for example to see if anything look like curve)
Either you approximate your curve with multiline and convert it back to
curve with postgis
(http://postgis.net/docs/ST_LineToCurve.html)

Or you have to go the long way and pass the curves as attributes, and
reconstruct it afterward.
This seems like a very bad idea.

I'm afraid curve support is very very limited in all gis tools.

Cheers,
Rémi-C

2014-11-12 0:00 GMT+01:00 Andy Colson a...@squeakycode.net
mailto:a...@squeakycode.net:

Hi All,

Not sure where the best place to ask this, so I'll start here.

I have a FileGDB that contains some curved lines, I import it into
PostGIS and they come out as chopped off Multiline.

Here are some screen shots to help explain:

Here is arc viewing the .gdb:
http://testmaps.camavision.__com/arc.png
http://testmaps.camavision.com/arc.png

Here is qgis viewing PostGIS:
http://testmaps.camavision.__com/qgis.png
http://testmaps.camavision.com/qgis.png


I tried to get arcCatalog to export to shapefile, but something went
bad and I cannot even look at the .shp files.

Any hints how I can get the curve back?

Thanks for your time,

-Andy


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

Re: [postgis-users] FileGDB curves

[Andy Colson]

Yeah, I think the info is gone, because ST_LineToCurve looks the same. 
As a test I changed my map file from:


DATA 'the_geom FROM washingtonmn.streets using unique gid using srid=3857'

TO:

DATA 'the_geom FROM (select gid, fullstreet, st_LineToCurve(the_geom) as 
the_geom from washingtonmn.streets) as subq using unique gid using 
srid=3857'


And it displays the same either way.

I am using FileGDB:

ogrinfo -so WCWebDataCC.gdb/
INFO: Open of `WCWebDataCC.gdb/'
  using driver `FileGDB' successful.


The command I'm using is:

ogr2ogr -f PostgreSQL
  -lco FID=gid
  -lco SPATIAL_INDEX=OFF
  -lco GEOMETRY_NAME=the_geom
  -lco SCHEMA=washingtonmn
  -lco PRECISION=NO
  -lco DIM=2
  -t_srs EPSG:3857
  -nlt MULTILINESTRING
  'PG:dbname=gis'
  WCWebDataCC.gdb

Is the -nlt MULTILINESTRING doing bad things?  Its in there because 
some times I get layers marked as LINESTRING that dont convert so I just 
up them to MULTILINESTRING to get it to go.


Thanks again,

-Andy

On 11/12/2014 9:03 AM, Rémi Cura wrote:


Good, you narrowed the error.

The function would do it for you,
it would convert the curved parts into curves and the straight part into
segment,
forming a curved polygon, which is a set of curve and segments.

But I'm affraid your line must appoximate the curves to have a result.
Your 2 first screen shots indicate that the curve information is simply
gone.

You can manually approximate curves into line in arcgis before export,
or maybe OGR driver for arcgis support curves
(http://www.gdal.org/drv_filegdb.html)

Cheers,
Rémi-C



2014-11-12 15:56 GMT+01:00 Andy Colson a...@squeakycode.net
mailto:a...@squeakycode.net:

Mapserver displays it the same way:

http://testmaps.camavision.__com/map/washingtonmn?pin=16.__028.21.11.0039
http://testmaps.camavision.com/map/washingtonmn?pin=16.028.21.11.0039

The data looks like:

gis=# select st_AsText(the_geom) from streets where gid = 11517;
-[ RECORD 1

]-__--__--__--__--__--__--__--__--__--__--__-
st_astext | MULTILINESTRING((-10344354.__4038468
5608573.22422463,-10344363.__9860388
5608565.68874797,-10344398.__9885982
5608528.81799097,-10344438.__5518419
5608468.68094317,-10344404.__2129173
5608265.5414558,-10344355.__0291817
5608227.92219317,-10344239.__0250123
5608196.3261761,-10344230.__7760714
5608197.59004929,-10344187.__3835781 5608212.14184435))



So I'm assuming that its a convert problem, not a display problem.

I'm not sure how to use ST_LineToCurve.  This is a road layer and
everything is a MULTILINESTRING, I can't tell the straight from the
curves.  Can I?

Thanks,

-Andy



On 11/12/2014 3:58 AM, Rémi Cura wrote:

Hey,
first qgis can't deal with curve (i think),
so your data may be curved in your postgis but the visualisation
would
be incorrect
   so if you are going to see some curve in qgis, you have to
convert the
curve to approximate theim using
http://postgis.net/docs/ST___CurveToLine.html
http://postgis.net/docs/ST_CurveToLine.html,
then display the geom
(you can do this in a view if you don't want to duplicate data)

Now if you are really really sure that data in postgis is bad (use
ST_AsTExt(geom) for example to see if anything look like curve)
Either you approximate your curve with multiline and convert it
back to
curve with postgis
(http://postgis.net/docs/ST___LineToCurve.html
http://postgis.net/docs/ST_LineToCurve.html)

Or you have to go the long way and pass the curves as
attributes, and
reconstruct it afterward.
This seems like a very bad idea.

I'm afraid curve support is very very limited in all gis tools.

Cheers,
Rémi-C

2014-11-12 0:00 GMT+01:00 Andy Colson a...@squeakycode.net
mailto:a...@squeakycode.net
mailto:a...@squeakycode.net mailto:a...@squeakycode.net__:

 Hi All,

 Not sure where the best place to ask this, so I'll start here.

 I have a FileGDB that contains some curved lines, I import
it into
 PostGIS and they come out as chopped off Multiline.

 Here are some screen shots to help explain:

 Here is arc viewing the .gdb:
http://testmaps.camavision.com/arc.png
 http://testmaps.camavision.__com/arc.png
http://testmaps.camavision.com/arc.png

 Here is qgis viewing PostGIS:
http://testmaps.camavision.com/qgis.png
 http://testmaps.camavision.__com

Re: [postgis-users] FileGDB curves

[Andy Colson]

Shoot.  I changed the command to:

ogr2ogr
  -f PostgreSQL
  -t_srs EPSG:3857
  'PG:dbname=gis'
  WCWebDataCC.gdb
  -lco FID=gid
  -lco SPATIAL_INDEX=OFF
  -lco GEOMETRY_NAME=the_geom
  -lco SCHEMA=washingtonmn
  -lco PRECISION=NO
  streets


(removing the -nlt) and I still get a MULTILINESTRING, and it still 
looks the same.


The ogr2ogr is part of a perl script, and the first command I posted was 
my guess at what it generated, which was a little off.  The command 
above is correct.


I'm not sure what else to try.

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

Re: [postgis-users] FileGDB curves

[Andy Colson]

I only use arc to convert stuff, otherwise I avoid it.  I don't even 
know how to edit with it.


This is only one curve, there are probably lots and lots in the layer.

Guess I'll try gdb - shapefile again.  Have had some luck with that in 
the past.


Thanks all,

-Andy

On 11/12/2014 10:15 AM, Rémi Cura wrote:

So it seems you are stuck with approximating curve with line inside
FileGDB before using ogr.

I guess you can do so using arcgis, but I never used it.

Cheers,
Rémi-C

2014-11-12 17:09 GMT+01:00 Paul Ramsey pram...@cleverelephant.ca
mailto:pram...@cleverelephant.ca:

Correct, the curves were likely ruined on the way through GDAL. In
some ways, fortunate they ever got through at all.

On Wed, Nov 12, 2014 at 7:48 AM, Bborie Park dustym...@gmail.com
mailto:dustym...@gmail.com wrote:
  If I remember correctly, curve geometries are not currently
supported in
  GDAL/OGR. There is a GDAL RFC making the rounds for adding curve
support...
 
  http://trac.osgeo.org/gdal/wiki/rfc49_curve_geometries
 
  -bborie
 
  On Wed, Nov 12, 2014 at 7:44 AM, Andy Colson
a...@squeakycode.net mailto:a...@squeakycode.net wrote:
 
  Shoot.  I changed the command to:
 
  ogr2ogr
-f PostgreSQL
-t_srs EPSG:3857
'PG:dbname=gis'
WCWebDataCC.gdb
-lco FID=gid
-lco SPATIAL_INDEX=OFF
-lco GEOMETRY_NAME=the_geom
-lco SCHEMA=washingtonmn
-lco PRECISION=NO
streets
 
 
  (removing the -nlt) and I still get a MULTILINESTRING, and it
still looks
  the same.
 
  The ogr2ogr is part of a perl script, and the first command I
posted was
  my guess at what it generated, which was a little off.  The
command above is
  correct.
 
  I'm not sure what else to try.
 
 
  -Andy
  ___
  postgis-users mailing list
  postgis-users@lists.osgeo.org mailto:postgis-users@lists.osgeo.org
  http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users
 
 
 
  ___
  postgis-users mailing list
  postgis-users@lists.osgeo.org mailto:postgis-users@lists.osgeo.org
  http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users
___
postgis-users mailing list
postgis-users@lists.osgeo.org mailto: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] FileGDB curves [SOLVED]

[Andy Colson]

On 11/12/2014 10:20 AM, Andy Colson wrote:


Guess I'll try gdb - shapefile again.  Have had some luck with that in
the past.



If I use ArcCatalog to convert GDB to Shapefile, then the shapefile 
displays nicely in QGis, but its in some weird projection that ogr2ogr 
cant seem to reproject correctly.  It runs but then PostGIS has lots of 
nans instead of numbers.


So I tried to use ArcCatalogs to re-project into EPSG:3857, and then use 
ogr2ogr to import into PostGIS.  Then I get this error:


ogr2ogr -f PostgreSQL -lco FID=gid -lco SPATIAL_INDEX=OFF -lco 
GEOMETRY_NAME=the_geom -lco SCHEMA=washingtonmn -lco PRECISION=NO -lco 
DIM=2 -t_srs EPSG:3857 -nlt MULTILINESTRING 'PG:dbname=gis' 
DimensionLeader.shp


ERROR 6: No translation for Mercator_Auxiliary_Sphere to PROJ.4 format 
is known.

Failed to create coordinate transformation between the
following coordinate systems.  This may be because they
are not transformable, or because projection services
(PROJ.4 DLL/.so) could not be loaded.
Source:
PROJCS[WGS_1984_Web_Mercator_Auxiliary_Sphere,
GEOGCS[GCS_WGS_1984,
DATUM[WGS_1984,
SPHEROID[WGS_84,6378137.0,298.257223563]],
PRIMEM[Greenwich,0.0],
UNIT[Degree,0.0174532925199433]],
PROJECTION[Mercator_Auxiliary_Sphere],
PARAMETER[False_Easting,0.0],
PARAMETER[False_Northing,0.0],
PARAMETER[Central_Meridian,0.0],
PARAMETER[Standard_Parallel_1,0.0],
PARAMETER[Auxiliary_Sphere_Type,0.0],
UNIT[Meter,1.0]]
Target:
PROJCS[WGS 84 / Pseudo-Mercator,
GEOGCS[WGS 84,
DATUM[WGS_1984,
SPHEROID[WGS 84,6378137,298.257223563,
AUTHORITY[EPSG,7030]],
AUTHORITY[EPSG,6326]],
PRIMEM[Greenwich,0,
AUTHORITY[EPSG,8901]],
UNIT[degree,0.0174532925199433,
AUTHORITY[EPSG,9122]],
AUTHORITY[EPSG,4326]],
PROJECTION[Mercator_1SP],
PARAMETER[central_meridian,0],
PARAMETER[scale_factor,1],
PARAMETER[false_easting,0],
PARAMETER[false_northing,0],
UNIT[metre,1,
AUTHORITY[EPSG,9001]],
AXIS[X,EAST],
AXIS[Y,NORTH],
EXTENSION[PROJ4,+proj=merc +a=6378137 +b=6378137 +lat_ts=0.0 
+lon_0=0.0 +x_0=0.0 +y_0=0 +k=1.0 +units=m +nadgrids=@null +wktext 
+no_defs],

AUTHORITY[EPSG,3857]]
ERROR 1: Terminating translation prematurely after failed
translation of layer DimensionLeader (use -skipfailures to skip errors)

Since the two didnt agree on what 3857 was, I tried 4326.  Here are the 
steps that finally got it to work.


In ArcCatalog:
  reproject WCWebDataCC.gdb to 4326 and save as junk.gdb
  batch convert junk.gdb to shapefile

ogr2ogr  shapefiles to PostGIS ( includes -t_srs EPSG:3857)

Which gives us, finally:

http://testmaps.camavision.com/map/washingtonmn?pin=16.028.21.11.0039


-Andy


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

[postgis-users] FileGDB curves

[Andy Colson]

Hi All,

Not sure where the best place to ask this, so I'll start here.

I have a FileGDB that contains some curved lines, I import it into 
PostGIS and they come out as chopped off Multiline.


Here are some screen shots to help explain:

Here is arc viewing the .gdb:
http://testmaps.camavision.com/arc.png

Here is qgis viewing PostGIS:
http://testmaps.camavision.com/qgis.png


I tried to get arcCatalog to export to shapefile, but something went bad 
and I cannot even look at the .shp files.


Any hints how I can get the curve back?

Thanks for your time,

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

Re: [postgis-users] setting up a read only user / group for AutoCAD, ArcGIS, QGIS, and MapServer.

[Andy Colson]

On 4/14/2014 7:25 AM, Mark Volz wrote:

Hello,

I would like to set up a user account in PostGIS / PostGRES with the
following:

·The user has read only access to all of the layers in a particular
database.

·The user also have read only access to any layers added or updated
through the shapefile uploader.

·The client software may be ArcGIS*, AutoCAD (Map), QGIS, MapServer, etc.

*ArcGIS will use “query layers”, not SDE.

If anyone has any cliff notes on how to properly  set up read only
permissions please let me know.

Thank You

Sincerely,

Mark Volz, GISP




You can think of users and groups pretty much the same.  user and 
role are mostly interchangeable.


Doesn't really matter who the owner of the db is, that can stay as-is.

We'll create a new role:

create user unwashed with password 'notpassword';
-- the difference between role and user is the can login right.  For 
me I was gonna grant them all login rights anyway.  You should be able 
to change the above to create role if you wanted to tighten it down.


-- grant it select
grant select on maintable to unwashed;

-- if you use sequences, they need rights
grant all on sequence maintable_id_seq to unwashed;

-- func's need exec:
grant execute on function update(userid integer) to unwashed;


-- There might be other's I'm missing.
-- Then create a new user in the unwashed group:

create user bob with nocreaterole password 'notpassword' in role unwashed;

Its simple to add/remove users now.  When you create new stuff, remember 
to grant the unwashed select rights. :-)


-Andy


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

Re: [postgis-users] Recent update to GDAL(?) broke PostGIS

[Andy Colson]

On 3/12/2014 12:43 PM, Jayson Gallardo wrote:

A while ago, our Ubuntu server was updated. One of the updates included
an update to gdal 1.10. Ever since then, our application no longer
works. PostGIS throws an error:

select postgis_full_version();
ERROR:  could not access file $libdir/rtpostgis-2.0: No such file
or directory
CONTEXT:  SQL statement SELECT postgis_gdal_version()
PL/pgSQL function postgis_full_version line 22 at SQL statement





How about reverting back to the prior gdal?

I assume the problem is rtpostgis-2.1.so is linked to gdal 1.9 or 
something.  You can check with ldd rtpostgis-2.1.so


I dunno where your rtpostgis-2.1.so would be.  Mine is in:
 /usr/local/pgsql/lib/postgresql

But your certainly wont be there.

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

Re: [postgis-users] Recent update to GDAL(?) broke PostGIS

[Andy Colson]

Sounds like the postgis package was uninstalled.  My debian foo is not 
strong, but does something like this return anything:


dpkg -s postgis
or
dpkg-query -l 'postgis*'


-Andy

On 3/12/2014 2:38 PM, Jayson Gallardo wrote:

That's the thing. I don't have a rtpostgis-2.x.so
http://rtpostgis-2.x.so anywhere on my system. I'm pretty sure it was
there before, because everything worked fine. And it may not even be
GDAL. I checked with the server admin and that was the only thing that
should have affected our setup.


On Wed, Mar 12, 2014 at 2:11 PM, Andy Colson a...@squeakycode.net
mailto:a...@squeakycode.net wrote:

On 3/12/2014 12:43 PM, Jayson Gallardo wrote:

A while ago, our Ubuntu server was updated. One of the updates
included
an update to gdal 1.10. Ever since then, our application no longer
works. PostGIS throws an error:

 select postgis_full_version();
 ERROR:  could not access file $libdir/rtpostgis-2.0: No
such file
 or directory
 CONTEXT:  SQL statement SELECT postgis_gdal_version()
 PL/pgSQL function postgis_full_version line 22 at SQL
statement




How about reverting back to the prior gdal?

I assume the problem is rtpostgis-2.1.so http://rtpostgis-2.1.so
is linked to gdal 1.9 or something.  You can check with ldd
rtpostgis-2.1.so http://rtpostgis-2.1.so

I dunno where your rtpostgis-2.1.so http://rtpostgis-2.1.so would
be.  Mine is in:
  /usr/local/pgsql/lib/__postgresql

But your certainly wont be there.

-Andy
_
postgis-users mailing list
postgis-users@lists.osgeo.org mailto:postgis-users@lists.osgeo.org
http://lists.osgeo.org/cgi-__bin/mailman/listinfo/postgis-__users
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] Severe performance problems with prepared statements

[Andy Colson]

On 1/7/2014 3:04 PM, William Becker wrote:



It looks like a bunch of row estimates are off.  Have you run analyze on the db?


Yeah - I did do that, or at least Vacuum analyzes.

Running an analyze now:

INFO:  analyzing public.place
INFO:  place: scanned 4134 of 4134 pages, containing 442283 live rows
and 0 dead rows; 3 rows in sample, 442283 estimated total rows
Total query runtime: 174 ms.

Here is the before/after explain analyzes on the query you wrote below:
you can see they still both do a seq scan.

Aggregate  (cost=119496.15..119496.16 rows=1 width=0) (actual
time=1617.616..1617.616 rows=1 loops=1)
  -  Seq Scan on place  (cost=0.00..119127.58 rows=147428 width=0)
(actual time=746.779..1617.611 rows=9 loops=1)
Filter:
st_covers('010320E61001000500D33CB60D9C3FB0941EB661BD4940D33CB60D9C3FFF56F03723BA49402DC349F290BFFF56F03723BA49402DC349F290BFB0941EB661BD4940D33CB60D9C3FB0941EB661BD4940'::geography,
location)
Total runtime: 1617.639 ms


Aggregate  (cost=119496.15..119496.16 rows=1 width=0) (actual
time=1618.655..1618.655 rows=1 loops=1)
  -  Seq Scan on place  (cost=0.00..119127.58 rows=147428 width=0)
(actual time=747.573..1618.649 rows=9 loops=1)
Filter:
st_covers('010320E61001000500D33CB60D9C3FB0941EB661BD4940D33CB60D9C3FFF56F03723BA49402DC349F290BFFF56F03723BA49402DC349F290BFB0941EB661BD4940D33CB60D9C3FB0941EB661BD4940'::geography,
location)
Total runtime: 1618.678 ms

Interestingly, it seems to be using the indexes on my dev database, so
try upgrading from 2.0-2.1 and 9.2-9.3 and redeploying the db and see
if that has an effect.

I'll try to do the upgrade over the next few days and then if that
works, the stored procedure again and let you know how it goes.

Thanks for the help Andy!


Weird that it doesn't use an index.  I see you are using Geography type, 
which was pretty new... maybe v2.0 st_covers() didnt use an index right.


If you still cannot get it to use and index, you might use a less 
restrictive (and probably quicker)  check:



select count(*)
from place,
st_GeogFromText('010320E61001000500D33CB60D9C3FB0941EB661BD4940D33CB60D9C3FFF56F03723BA49402DC349F290BFFF56F03723BA49402DC349F290BFB0941EB661BD4940D33CB60D9C3FB0941EB661BD4940') 
as g

where g  location and st_covers(g, location)

The  might return too many records, but the st_covers() will drop 
them.   is sure to be indexed, and is probably calculated faster 
because it's just bounding box.


-Andy


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

Re: [postgis-users] Severe performance problems with prepared statements

[Andy Colson]

On 01/05/2014 11:12 AM, William Becker wrote:

I just tried changing my code base from using hard coded queries to prepared 
statements. As part of this I pass a parameter as the argument to the 
ST_GeographyFromText() function. You can see the code here (1).

After doing this, it took ~20 seconds to run, when previously it took ~2 
seconds. I thought this might be because it wasn't using an index anymore, but 
I checked this out - when it was previously taking 2 seconds, it also ran 
without using the index. For some reason it's using it on my dev machine, but 
this has a smaller dataset - I'll try tuning this but it does not seem to be 
the cause of the issue.

I improved this by templatising the query, so that a new query is always 
generated with a different hard coded geography, and this speeds things up - code is here 
(2). However, it now needs to create a separate query for each execution, thus it also 
needs to generate a new plan every time, which is not ideal.

I have attached query plans for the slow run (3) and the sped up run (4). (I 
apologise that the queries aren't exactly the same in each case, but the 
timings are pretty much the same regardless of what the other parameters are).

You can also see the create statement for the database here: (5)

I'm not entirely sure if the problem lies with postgis or postgres in general, 
but I thought I'd present it to you nice people first, since you are probably 
in a better position to decide! This is on postgis 2.0 with postgres 9.2. I 
just upgraded to 9.3 and 2.1 but I need to re-process all my data (which will 
take overnight) before I can do anything.

My guess is that it is evalutating the parameter each time it applies the where 
in the query and there is something slow about this. I'd poke around the 
relavant code if someone could give me some pointers about where to look!

I've seem some other threads that seem to be related:
http://postgresql.1045698.n5.nabble.com/avoid-prepared-statements-on-complex-queries-td4996363.html
http://grokbase.com/t/postgresql/pgsql-performance/127rp189dw/geoserver-postgis-performance-problems

but it seems that I have isolated it just down to that scan taking a long time 
with the prepared statement, but quick without it?

Cheers,
Will

(1) 
https://github.com/twistedvisions/anaximander/blob/c0a7c89e438bcf2504ed8ae3d5f75828e11c2ec9/db_templates/get_event_attendees_in_area.sql

(2) 
https://github.com/twistedvisions/anaximander/blob/12e65537f1d3887677b592d1f0ac0203f2da7d39/db_templates/get_event_attendees_in_area.sql

(3) http://explain.depesz.com/s/kb1A

(4) http://explain.depesz.com/s/G9Wj

(5) 
https://github.com/twistedvisions/anaximander/blob/f6ab21ebff4cf3541bfb53715addb4b3181d5d2e/db/create.sql




It looks like a bunch of row estimates are off.  Have you run analyze on the db?


a new query is always generated with a different hard coded geography.  
However, it now needs to create a separate query for each execution, thus it 
also needs to generate a new plan every time, which is not ideal.


Actually, creating a plan can use information about the data to make a better plan.  That's why using a 
$1 parameter may not give optimal plans.  When you say where id  5, the planer knows the 
distribution of data, so it knows about the probability's of 5.  But when you say where id  
$1, it has to just use a generic plan.

As long as you dont have your stats target too high, plan time is very fast, 
and compared to a bad plan, could easily be worth paying the price to re-plan 
on every execute.

Any reason you are using HASH indexes?  They usually aren't better.  Have you 
tried it with btree?

Hum?  As a simple test, does this use an index:

select count(*) from place
where 
st_covers('010320E61001000500D33CB60D9C3FB0941EB661BD4940D33CB60D9C3FFF56F03723BA49402DC349F290BFFF56F03723BA49402DC349F290BFB0941EB661BD4940D33CB60D9C3FB0941EB661BD4940'::geography,
 location)

After you analyze does it?

Does this:

prepare x as select count(*) from place where st_covers($1::geography, 
location);
explain analyze execute x('0103');
deallocate x;


-Andy

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

[postgis-users] add custom epsg

[Andy Colson]

I know this isn't directly a postgis question, I'm kinda hoping someone 
will know anyway.


I'm trying to import a File.gdb into postgis, but the projection its in 
seems to be unknown.


I'm using ogr2ogr to do the conversion (and all my scripts are already 
setup to use it, so I'd rather not change to shp2pgsql), and I'm getting 
this error:


ERROR 6: EPSG PCS/GCS code 103782 not found in EPSG support files.  Is 
this a valid EPSG coordinate system?


The data is for Steele MN, and this does seem to be a thing:

http://edndoc.esri.com/arcims/9.2/elements/pcs.htm#103782

so how can I get ogr2ogr (ogrinfo, testepsg, etc) to use it?

Thanks for your time,

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

Re: [postgis-users] add custom epsg

[Andy Colson]

2) I'm aware of the PostGIS tables but I'm pretty sure ogr2ogr isn't 
using them.


1) Sweet!  That's what I was looking for.  The proj was, obviousely, in 
proj format, not wkt, which is all I had.  But gdalsrsinfo was able to 
create a proj definition which I added to /usr/share/proj/epsg, and then 
it ran.


Thank you much.

-Andy


On 12/19/2013 11:43 AM, Greg Allensworth wrote:

1.
The GDAL/OGR tools use PROJ to do the reprojection, and PROJ knows about
projections, via its epsg file. On Unix systems this is something
like:  /usr/share/proj/epsg

Pop it open, and you'll see that the format is quite easy to follow.
Paste in a new pair of lines, and that SRS is now recognized by ogr2ogr.

2.
PostGIS does not use the PROJ epsg file, but instead the spatial_ref_sys
DB table. This lists projections available to PostGIS, and if you do a
SELECT there you should find the rows easy to understand: srid (103782),
authority (EPSG), the SRS in both PROJ format and WKT format, ... Simply
insert a row here and PostGIS will support that SRS.


I hope that helps!


On 12/19/2013 9:12 AM, Andy Colson wrote:

I know this isn't directly a postgis question, I'm kinda hoping someone
will know anyway.

I'm trying to import a File.gdb into postgis, but the projection its in
seems to be unknown.

I'm using ogr2ogr to do the conversion (and all my scripts are already
setup to use it, so I'd rather not change to shp2pgsql), and I'm getting
this error:

ERROR 6: EPSG PCS/GCS code 103782 not found in EPSG support files.  Is
this a valid EPSG coordinate system?

The data is for Steele MN, and this does seem to be a thing:

http://edndoc.esri.com/arcims/9.2/elements/pcs.htm#103782

so how can I get ogr2ogr (ogrinfo, testepsg, etc) to use it?

Thanks for your time,

-Andy
___
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] CTE example

[Andy Colson]

On 03/05/2013 01:52 PM, Patrick Browne wrote:

I am trying to run the CTE example on slide 22 from 
http://www.postgis.us/downloads/oscon2009_PostGISTips.pdf

When I paste the example into the SQL-shell I get the following error:

 ERROR: operator does not exist: record - integer LINE 14: ... x, y, 
ST_SetSRID(ST_MakeBox2d(ST_Point(xmin + (x - 1)*g_wid... HINT: No operator 
matches the given name and argument type(s). You might need to add explicit 
type casts

I am using POSTGIS 1.4.1 with Postgres 8.4 I used the data that accompany the 
slides.


Any advice will be much appreciated.



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



I didn't want to read the pdf, so going just based on the text above, I'd say 
that (x - 1) isnt what you think.

x isn't an integer at this point, its one row of a table maybe?  Do you have 
more than one thing named x?

I'm assuming PG is looking for an operator - (minus) that takes a record and 
an integer as arguments.

Thats my interpretation of operator does not exist: record - integer

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

Re: [postgis-users] Partitions

[Andy Colson]

On 3/1/2013 12:25 PM, oisink wrote:

Hi

I'm about to build a large spatial database (OS MasterMap - ~440M features).
I intend constraining the partitions against 25km grid squares. However,
using this approach some features will inevitably cross the boundary between
my partition constraints. Can this be done?

How does it work in practice, i.e. when loading data do I need to check I am
not putting 'trans-partition' features into more than one partition, or do I
duplicate them across partitions?

Regards

O



Are you going to be deleting and re-adding huge chunks of data?  I 
believe that's what partitions are best for.  If your dataset is mostly 
read-only, then partitions wont help.


From what I've read, you should not duplicate, otherwise if you select 
* from parent you will get duplicate records back.


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

Re: [postgis-users] design problem

[Andy Colson]

On 3/1/2013 3:11 PM, Denise Janson wrote:

Hi,

I have an application that is going to receive lots of georeferenced files
every day. Each file has information of several points. Probably in few
years my application will have a Tera of points information stored.

I think I can do this design  in two ways:

1. Two tables, one of “uploaded_files”, and another of “points” (one
uploadedFile to N points). And I'll have to partition the points table,
maybe by month …
2. Or I can create one table per file, having thousands of tables in few
years.

Which case is better for my application?  Is there any better way to do
this?



If performance is a concern, and the file's are of any meaningful size, 
you might consider leaving them on the filesystem and have the table 
point to it (full path name sort of thing).


Storing the file in PG is possible, and its nice because everything is 
kept together, but if you have to get to and read the files fast, then 
leave them on the filesystem.


The lots of tables approach is problematic if you ever want to write 
queries that look back in time.  Its much harder to say, give me every 
record from the beginning of time at this point.


With a good index, PG wont have a problem with a single table containing 
billions of rows.  Just try to avoid doing bulk operations (like update 
and delete) on the entire table.



 uploadedFile to N points). And I'll have to partition the points table,


Why will you have to partition it?

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

Re: [postgis-users] UPDATE a value using trigger and ST_Contains

[Andy Colson]

On 02/21/2013 06:37 PM, isla...@infomed.sld.cu wrote:

Hello list, i have a doubt, i have 2 tables
t1 and t2
and t1 has many t2, that's the constraint, but i need a trigger that update the 
value of the FK on the t2 table
cause i need to put the t1.gid on t2.munic_id automaticly when i update and 
insert, and i make this:


BEGIN

IF ST_Contains(t1.the_geom,ST_Centroid(NEW.the_geom)) = TRUE THEN
 NEW.munic_id = t1.gid;
 END IF;

RETURN NEW;

END;

and i get this error


ERROR: falta una entrada para la tabla t1 en la clausula FROM

please what it means??, i need help




I'll make a guess.  You need to write it as a select statement:

begin

  select t1.gid from t1 where ST_Contains(t1.the_geom,ST_Centroid(NEW.the_geom)) info 
NEW.munic_id;
  return NEW;

end;


Or something like that.

-Andy

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

Re: [postgis-users] UPDATE a value using trigger and ST_Contains

[Andy Colson]

On 02/21/2013 08:53 PM, Andy Colson wrote:



   select t1.gid from t1 where ST_Contains(t1.the_geom,ST_Centroid(NEW.the_geom)) info 
NEW.munic_id;



oops.  s/info/into/

-Andy



double oops.  The into goes first:

select t1.gid into NEW.munic_id from t1 where 
ST_Contains(t1.the_geom,ST_Centroid(NEW.the_geom));

I really hope I eventually get this right :-)

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