Re: [postgis-users] Postgress.app + pointcloud
Hi, 2016-12-18 21:40 GMT+01:00 Puneet Kishor : > As advised, I downloaded and installed Postgress.app (Mac OS X Sierra with > Xcode 8.1). All works well. > > However, I'd like to install and experiment with the pointcloud extension > as I have a bunch of data from a 3D scan. Since I don't have the dev > version of Pg, I can't really compile Pc against my Postgress.app. Is it > possible to find a pointcloud binary somewhere? Or is there another > suggested path forward? > I generally work just with sources in Mac OS. I'd suggest to compile PostgreSQL, and then pgPointCloud. I use this: https://github.com/mnencia/pgenv that is able to install and configure different PostgreSQL versions instances, including dev tools, debug symbols, etc., useful if you have to develop something related with PostgreSQL. I already used PostgreSQL 9.6 + pgPointCloud 1.1 in my Mac OS X Sierra laptop in this way. Regards, Giuseppe. -- Giuseppe Broccolo - 2ndQuadrant Italy PostgreSQL & PostGIS Training, Services and Support giuseppe.brocc...@2ndquadrant.it | www.2ndQuadrant.it ___ postgis-users mailing list postgis-users@lists.osgeo.org http://lists.osgeo.org/mailman/listinfo/postgis-users
Re: [postgis-users] Postgress.app + pointcloud
> On Dec 19, 2016, at 10:20 AM, Giuseppe Broccolo > wrote: > > Hi, > > 2016-12-18 21:40 GMT+01:00 Puneet Kishor : >> As advised, I downloaded and installed Postgress.app (Mac OS X Sierra with >> Xcode 8.1). All works well. >> >> However, I'd like to install and experiment with the pointcloud extension as >> I have a bunch of data from a 3D scan. Since I don't have the dev version of >> Pg, I can't really compile Pc against my Postgress.app. Is it possible to >> find a pointcloud binary somewhere? Or is there another suggested path >> forward? > > I generally work just with sources in Mac OS. I'd suggest to compile > PostgreSQL, and then pgPointCloud. I use this: > > https://github.com/mnencia/pgenv > > that is able to install and configure different PostgreSQL versions > instances, including dev tools, debug symbols, etc., useful if you have to > develop something related with PostgreSQL. I already used PostgreSQL 9.6 + > pgPointCloud 1.1 in my Mac OS X Sierra laptop in this way. > Ouch, I was hoping to avoid this path as it means not using my Postgress.app instance anymore, but I may have to do this if I can’t find a binary that works with Postgress.app. Thanks anyway. Puneet ___ postgis-users mailing list postgis-users@lists.osgeo.org http://lists.osgeo.org/mailman/listinfo/postgis-users
Re: [postgis-users] Query choces on searching too small area
Yes, this works nicely. Thanks! Didn’t know about the ‘WITH’ statement
From: postgis-users [mailto:postgis-users-boun...@lists.osgeo.org] On Behalf Of
Rémi Cura
Sent: vrijdag 16 december 2016 18:33
To: PostGIS Users Discussion
Subject: Re: [postgis-users] Query choces on searching too small area
Hey, there is a dedicated "slow query" protocol on postgres user list, and its
quite sane.
For instance, it would suggest you to give the version number you use, your
hardware, etc etc.
About you query, I guess your photo are geotagged (i.e. each photo is a point,
and maybe you have a precision attribute).
Using ST_Intersects thus could be replaced by ST_DWithin with your precision /
default tolerancey, which is safer.
Index not kicking may have many causes, such as outdated statistics (have you
vacuum analyse -ed often?), wrong config regarding your hardware
(seq_page_cost, and so),
poorly written query, etc.
Anyway your query should not freeze on only 500k geometries, so I'm also
guessing that in the table "photo" you not only store photo point / geometry,
but also the binary of the photo, which is bound to be dangerous.
So steps to fix your problem
- update postgres / postgis if you can
- check stats / vacuum
- check postgres config
- rewrite your query for a better form (see example 1 )
- post on list, this might be a bug
- rewrite query to force to perform first geometry test then the other
(example query 2)
- create a "proxy" photo_proxy table that contains only photo_id and
photo.geometry
- force use of index via settings (usually a very bad idea)
- ...
Here is how your query could be simplified :
SELECT
"id",
"filename",
ST_AsText("geometry") AS "geometry",
ST_AsText("center") AS "center",
"angle"
FROM "photo"
WHERE (ST_DWithin("geometry", st_GeomFromText( 'POINT(4.5063099203616
51.923602970634)', 4326),your_precision))
AND "id" IN (
SELECT DISTINCT "photoId"
FROM "photoMetadata"
WHERE ("value" = 'KADASTER') AND ("key" = 'source')))
AND ("photoId" IN (
SELECT DISTINCT "photoId"
FROM "photoMetadata"
WHERE (key = 'year' AND ( cast(value as int ) >= 1866 AND cast ( value as int )
<= 1981))
))
ORDER BY "filename" LIMIT 36
here is the query to force use of geometric index :
WITH photos_spatialy_close AS (
SELECT id AS photoId
FROM photo
WHERE ST_DWithin("geometry", st_GeomFromText( 'POINT(4.5063099203616
51.923602970634)', 4326),your_precision)
LIMIT 36
)
, photo_with_correct_metatadata AS (
SELECT DISTINCT "photoId"
FROM "photoMetadata"
WHERE ("value" = 'KADASTER')
AND ("key" = 'source')))
AND ("photoId" IN (
SELECT DISTINCT "photoId"
FROM "photoMetadata"
WHERE (key = 'year'
AND ( cast(value as int ) >= 1866
AND cast ( value as int ) <= 1981))
)
, keeping_photo_id_in_both_set AS (
SELECT photoId
FROM photos_spatialy_close
INTERSECTS
SELECT photoId
FROM photo_with_correct_metatadata
)
SELECT
"id",
"filename",
ST_AsText("geometry") AS "geometry",
ST_AsText("center") AS "center",
"angle"
FROM keeping_photo_id_in_both_set LEFT OUTER JOIN photo ON ( photoId = id)
LIMIT ...
Cheers
Remi-C
2016-12-16 17:48 GMT+01:00 Sandro Santilli mailto:s...@kbt.io>>:
On Fri, Dec 16, 2016 at 03:33:32PM +0100, Arjen Haayman wrote:
> QUERY PLAN
[...]
> What does this tell you?
That your query is too complex ?
Check out http://explain.depesz.com
--strk;
___
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] Query choces on searching too small area
@Paul, that's what I thought, but it seems querry can't be un-rolled like this, because one row will have only one key. (no one row will match all the conditions, you have to get several rows and perform intersection of results). I fully agree this is totally bad for postgres, I would guess Arjen use a off-the-shelf module and as no choice over architecture. Cheers, Rémi-C 2016-12-16 18:59 GMT+01:00 Paul Ramsey : > Two things: > > (a) I'm curious what happens when you unroll all those embedded subqueries > and let the planner try to do what it does best, something like this: > > SELECT DISTINCT ON (photo.id) > photo.id, > photo.filename, > ST_AsText(photo.geometry) AS geometry, > ST_AsText(photo.center) AS center, > photo.angle > FROM photo > JOIN "photoMetadata" meta > ON photo.id = meta."photoId" > WHERE (key = 'source' AND value = 'KADASTER') > AND (key = 'year' AND value::int BETWEEN 1866 AND 1981) > AND ST_Intersects(photo.geometry, ST_SetSRID(ST_MakePoint(4.5063099203616, > 51.923602970634), 4326)) > ORDER BY photo.filenam > > (b) You should strongly consider changing your metadata table from the > key/value table into a jsonb table, with the metadata in JSON. Then for > things like date, and source, you can build functional indexes to allow > fast filtering on those common metadata fields, while still allowing fully > free-form metadata objects. This would make the whole thing both simpler > and a lot faster. > > P. > > > > On Fri, Dec 16, 2016 at 8:48 AM, Sandro Santilli wrote: > >> On Fri, Dec 16, 2016 at 03:33:32PM +0100, Arjen Haayman wrote: >> > QUERY PLAN >> >> [...] >> >> > What does this tell you? >> >> That your query is too complex ? >> Check out http://explain.depesz.com >> >> --strk; >> ___ >> 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
