[postgis-users] postgis-users Digest, Vol 149, Issue 6
Alexander W. Rolek
a.rolek at gmail.com
Wed Jul 9 17:47:45 PDT 2014
I tried the query that Remi suggested (thank you!) and it works and doesn't
work. I have the exact same table in two different projections (2230,
4326). When I run the ST_Dwithin query against the 2230 table, I get
results in under 100 ms. When I run the same query against the 4326
projection it still takes around 270 seconds! I have confirmed that I have
the gist index on the 4326 table. Any ideas why the 4326 projection would
be dramatically slower?
On Wed, Jul 9, 2014 at 12:00 PM, <postgis-users-request at lists.osgeo.org>
wrote:
> Send postgis-users mailing list submissions to
> postgis-users at lists.osgeo.org
>
> To subscribe or unsubscribe via the World Wide Web, visit
> http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users
> or, via email, send a message with subject or body 'help' to
> postgis-users-request at lists.osgeo.org
>
> You can reach the person managing the list at
> postgis-users-owner at lists.osgeo.org
>
> When replying, please edit your Subject line so it is more specific
> than "Re: Contents of postgis-users digest..."
>
>
> Today's Topics:
>
> 1. Error with Ubuntu installation from PostgreSQL APT repository
> (Matthew Baker)
> 2. Re: Error with Ubuntu installation from PostgreSQL APT
> repository (Stephen V. Mather)
> 3. Finding records withing XX distance from a geometry in the
> same table (Alexander W. Rolek)
> 4. Re: Finding records withing XX distance from a geometry in
> the same table (Guido Lemoine)
> 5. Re: Finding records withing XX distance from a geometry in
> the same table (R?mi Cura)
> 6. Re: Finding records withing XX distance from a geometry in
> the same table (tommaso)
>
>
> ----------------------------------------------------------------------
>
> Message: 1
> Date: Tue, 08 Jul 2014 16:44:28 -0600
> From: Matthew Baker <mattbaker at gmail.com>
> To: PostGIS Users Discussion <postgis-users at lists.osgeo.org>
> Subject: [postgis-users] Error with Ubuntu installation from
> PostgreSQL APT repository
> Message-ID: <53BC744C.7040004 at gmail.com>
> Content-Type: text/plain; charset="iso-8859-1"; Format="flowed"
>
> Hi all,
>
> Last year I wrote up some PostGIS installation instructions for Ubuntu
> based on the PostgreSQL APT repository:
>
> http://trac.osgeo.org/postgis/wiki/UsersWikiPostGIS21UbuntuPGSQL93Apt
>
> These worked great for a while, but seems to have stopped working with
> Ubuntu 14.04.
>
> I know there was a thread back at the end of January discussing the
> version of GDAL that would work with PostGIS (1.9 / 1.10) , but if
> nothing else, here's an update on the installation error messages:
>
> --When running this on a fresh Ubuntu 14.04 VM - with no QGIS installed
> (thanks Stephen), here are the messages that I get:
>
> After running this command:
>
> *sudo apt-get install postgresql-9.3 postgresql-9.3-postgis pgadmin3
> postgresql-contrib*
>
> I get the message:
>
> /Package postgresql-9.3-postgis is not available, but is referred to by
> another package.//
> //This may mean that the package is missing, has been obsoleted, or//
> //is only available from another source//
> //
> //E: Package 'postgresql-9.3-postgis' has no installation candidate/
>
> So I modified the install command specific to PostGIS:
>
> *sudo apt-get install postgresql-9.3 /postgis /pgadmin3 postgresql-contrib
> *
>
> Which gives the message:
>
> /Some packages could not be installed. This may mean that you have//
> //requested an impossible situation or if you are using the unstable//
> //distribution that some required packages have not yet been created//
> //or been moved out of Incoming.//
> //The following information may help to resolve the situation://
> //
> //The following packages have unmet dependencies://
> // postgis : Depends: libgdal1 (>= 1.9.0) but it is not going to be
> installed//
> //E: Unable to correct problems, you have held broken packages.//
> /
> So I tried to install libgdal1 v 1.10.0 via Synaptic Package Manager,
> but it doesn't seem to help.
>
> That's where I'm stuck.
>
> Does anyone have any info either regarding this error, or if the
> PostgreSQL APT repo is still packaging PostGIS?
>
> I will try this on Ubuntu 12.04.4 just for fun...
>
> I appreciate any feedback! Thank you!!!
>
> -m
>
>
>
>
> -------------- next part --------------
> An HTML attachment was scrubbed...
> URL: <
> http://lists.osgeo.org/pipermail/postgis-users/attachments/20140708/0b24d96a/attachment-0001.html
> >
>
> ------------------------------
>
> Message: 2
> Date: Tue, 8 Jul 2014 23:40:33 +0000
> From: "Stephen V. Mather" <svm at clevelandmetroparks.com>
> To: PostGIS Users Discussion <postgis-users at lists.osgeo.org>
> Subject: Re: [postgis-users] Error with Ubuntu installation from
> PostgreSQL APT repository
> Message-ID:
> <
> c53f89f0a2e24579ac8036a3059c1c08 at DM2PR05MB734.namprd05.prod.outlook.com>
>
> Content-Type: text/plain; charset="iso-8859-1"
>
> Related, I started last night with a fork of vpicavet's docker/pggis as a
> fresh installer for PostGIS and friends for Ubuntu 14.4. It's not fully
> functional yet but serves as a sign post for installs on Ubuntu.
>
> Where would one start in doing package management for Debian / Ubuntu?
> Seems an ongoing issue for these flavors...
>
> https://github.com/smathermather/labr-pdal/blob/master/bootstrap.sh
>
> On Jul 8, 2014 6:44 PM, Matthew Baker <mattbaker at gmail.com> wrote:
> Hi all,
>
> Last year I wrote up some PostGIS installation instructions for Ubuntu
> based on the PostgreSQL APT repository:
>
> http://trac.osgeo.org/postgis/wiki/UsersWikiPostGIS21UbuntuPGSQL93Apt
>
> These worked great for a while, but seems to have stopped working with
> Ubuntu 14.04.
>
> I know there was a thread back at the end of January discussing the
> version of GDAL that would work with PostGIS (1.9 / 1.10) , but if nothing
> else, here's an update on the installation error messages:
>
> --When running this on a fresh Ubuntu 14.04 VM - with no QGIS installed
> (thanks Stephen), here are the messages that I get:
>
> After running this command:
>
> sudo apt-get install postgresql-9.3 postgresql-9.3-postgis pgadmin3
> postgresql-contrib
>
> I get the message:
>
> Package postgresql-9.3-postgis is not available, but is referred to by
> another package.
> This may mean that the package is missing, has been obsoleted, or
> is only available from another source
>
> E: Package 'postgresql-9.3-postgis' has no installation candidate
>
> So I modified the install command specific to PostGIS:
>
> sudo apt-get install postgresql-9.3 postgis pgadmin3 postgresql-contrib
>
> Which gives the message:
>
> Some packages could not be installed. This may mean that you have
> requested an impossible situation or if you are using the unstable
> distribution that some required packages have not yet been created
> or been moved out of Incoming.
> The following information may help to resolve the situation:
>
> The following packages have unmet dependencies:
> postgis : Depends: libgdal1 (>= 1.9.0) but it is not going to be installed
> E: Unable to correct problems, you have held broken packages.
>
> So I tried to install libgdal1 v 1.10.0 via Synaptic Package Manager, but
> it doesn't seem to help.
>
> That's where I'm stuck.
>
> Does anyone have any info either regarding this error, or if the
> PostgreSQL APT repo is still packaging PostGIS?
>
> I will try this on Ubuntu 12.04.4 just for fun...
>
> I appreciate any feedback! Thank you!!!
>
> -m
>
>
>
>
> -------------- next part --------------
> An HTML attachment was scrubbed...
> URL: <
> http://lists.osgeo.org/pipermail/postgis-users/attachments/20140708/44cba966/attachment-0001.html
> >
>
> ------------------------------
>
> Message: 3
> Date: Tue, 8 Jul 2014 20:36:14 -0700
> From: "Alexander W. Rolek" <a.rolek at gmail.com>
> To: postgis-users at lists.osgeo.org, Jeffrey Johnson
> <ortelius at gmail.com>
> Subject: [postgis-users] Finding records withing XX distance from a
> geometry in the same table
> Message-ID:
> <
> CAKvx66W7QJUPj+rZDedb_-hNb+50Tq-YGqdgcfr1cq5+eB9UXA at mail.gmail.com>
> Content-Type: text/plain; charset="utf-8"
>
> I'm trying to query all the records within XX distance from a geometry. I
> would like to be able to reference the subject geometry via it's id, and
> then find every geometry that is either:
>
> A) within XX distance from a x/y coordinate of the geometry
> B) within XX distance from the edge of the geometry on all sides. (ideal)
>
> What is the best way to accomplish this? My current query:
>
> SELECT *
> FROM sdgis.parcels4326 AS parcels
> WHERE ST_Distance_Sphere(
> parcels.geom,
>
>
> ST_Transform(ST_SetSRID(ST_MakePoint('6255896.314288','1898596.929109'),2230),4326)
> ) < 100.0;
>
> This takes a few minutes to run on a table with just over 1 million
> records. I would like to swap out the ST_MakePoint, with a multipolygon
> from a record that is looked up via it's id.
>
> Any suggestions are greatly appreciated.
>
> --
> Alexander W. Rolek
> -------------- next part --------------
> An HTML attachment was scrubbed...
> URL: <
> http://lists.osgeo.org/pipermail/postgis-users/attachments/20140708/5a8433ee/attachment-0001.html
> >
>
> ------------------------------
>
> Message: 4
> Date: Wed, 09 Jul 2014 09:25:59 +0200
> From: Guido Lemoine <guido.lemoine at jrc.ec.europa.eu>
> To: "'PostGIS Users Discussion'" <postgis-users at lists.osgeo.org>
> Subject: Re: [postgis-users] Finding records withing XX distance from
> a geometry in the same table
> Message-ID: <002f01cf9b47$07bd8d40$1738a7c0$@jrc.ec.europa.eu>
> Content-Type: text/plain; charset="us-ascii"
>
> Alexander,
>
>
>
> You would want to check out ST_BUFFER, which is doing what you intend to
> do.
>
>
>
> Guido
>
>
>
>
>
> From: postgis-users-bounces at lists.osgeo.org [mailto:
> postgis-users-bounces at lists.osgeo.org] On Behalf Of Alexander W. Rolek
> Sent: 09 July 2014 05:36
> To: postgis-users at lists.osgeo.org; Jeffrey Johnson
> Subject: [postgis-users] Finding records withing XX distance from a
> geometry in the same table
>
>
>
> I'm trying to query all the records within XX distance from a geometry. I
> would like to be able to reference the subject geometry via it's id, and
> then find every geometry that is either:
>
>
>
> A) within XX distance from a x/y coordinate of the geometry
>
> B) within XX distance from the edge of the geometry on all sides. (ideal)
>
>
>
> What is the best way to accomplish this? My current query:
>
>
>
> SELECT *
>
> FROM sdgis.parcels4326 AS parcels
>
> WHERE ST_Distance_Sphere(
>
> parcels.geom,
>
>
> ST_Transform(ST_SetSRID(ST_MakePoint('6255896.314288','1898596.929109'),2230),4326)
>
> ) < 100.0;
>
>
>
> This takes a few minutes to run on a table with just over 1 million
> records. I would like to swap out the ST_MakePoint, with a multipolygon
> from a record that is looked up via it's id.
>
>
>
> Any suggestions are greatly appreciated.
>
>
>
> --
> Alexander W. Rolek
>
> -------------- next part --------------
> An HTML attachment was scrubbed...
> URL: <
> http://lists.osgeo.org/pipermail/postgis-users/attachments/20140709/16f3bedc/attachment-0001.html
> >
>
> ------------------------------
>
> Message: 5
> Date: Wed, 9 Jul 2014 09:34:52 +0200
> From: R?mi Cura <remi.cura at gmail.com>
> To: PostGIS Users Discussion <postgis-users at lists.osgeo.org>
> Subject: Re: [postgis-users] Finding records withing XX distance from
> a geometry in the same table
> Message-ID:
> <
> CAJvUf_seWmCRcmsf_fHJxhyD4-hO_K672POUxB0GhpxAz_3cTQ at mail.gmail.com>
> Content-Type: text/plain; charset="utf-8"
>
> Hey,
> please _don't_ use st_buffer
> , but ST_DWithin (http://postgis.net/docs/ST_DWithin.html).
>
> This querry should be around 30 ms.
>
> You need to add an index to the geometry :
> CREATE INDEX parcels_geom_gist_index ON sdgis.parcels4326 USING GIST(geom)
> ;
>
> Now if you want to use the field "id", you also need an index on it (unless
> it is already a primary key):
> CREATE INDEX parcels_id_btree_index ON sdgis.parcels4326 (id) ;
>
> Depending on your use case you may need to use the GEOGRAPHY type instead
> of the geometry.
>
> Your querry could look like this (there could be more efficicient way, at
> the price of readability)
>
> WITH my_input_polygon AS (
> SELECT *
> FROM sdgis.parcels4326 AS parcels
> WHERE id = #ID_OF_TARGET_POLYGON#
> )
> SELECT p.*
> FROM my_input_polygon AS mip
> INNER JOIN sdgis.parcels4326 AS p ON
> (ST_DWITHIN(mip.geom,p.geom,100)=TRUE);
>
> It sholdn't take more than few dozen milliseconds.
>
> Cheers,
> R?mi-C
>
>
>
>
> 2014-07-09 9:25 GMT+02:00 Guido Lemoine <guido.lemoine at jrc.ec.europa.eu>:
>
> > Alexander,
> >
> >
> >
> > You would want to check out ST_BUFFER, which is doing what you intend to
> > do.
> >
> >
> >
> > Guido
> >
> >
> >
> >
> >
> > *From:* postgis-users-bounces at lists.osgeo.org [mailto:
> > postgis-users-bounces at lists.osgeo.org] *On Behalf Of *Alexander W. Rolek
> > *Sent:* 09 July 2014 05:36
> > *To:* postgis-users at lists.osgeo.org; Jeffrey Johnson
> > *Subject:* [postgis-users] Finding records withing XX distance from a
> > geometry in the same table
> >
> >
> >
> > I'm trying to query all the records within XX distance from a geometry. I
> > would like to be able to reference the subject geometry via it's id, and
> > then find every geometry that is either:
> >
> >
> >
> > A) within XX distance from a x/y coordinate of the geometry
> >
> > B) within XX distance from the edge of the geometry on all sides. (ideal)
> >
> >
> >
> > What is the best way to accomplish this? My current query:
> >
> >
> >
> > SELECT *
> >
> > FROM sdgis.parcels4326 AS parcels
> >
> > WHERE ST_Distance_Sphere(
> >
> > parcels.geom,
> >
> >
> >
> ST_Transform(ST_SetSRID(ST_MakePoint('6255896.314288','1898596.929109'),2230),4326)
> >
> > ) < 100.0;
> >
> >
> >
> > This takes a few minutes to run on a table with just over 1 million
> > records. I would like to swap out the ST_MakePoint, with a multipolygon
> > from a record that is looked up via it's id.
> >
> >
> >
> > Any suggestions are greatly appreciated.
> >
> >
> >
> > --
> > Alexander W. Rolek
> >
> > _______________________________________________
> > postgis-users mailing list
> > postgis-users at lists.osgeo.org
> > http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users
> >
> -------------- next part --------------
> An HTML attachment was scrubbed...
> URL: <
> http://lists.osgeo.org/pipermail/postgis-users/attachments/20140709/ab30650e/attachment-0001.html
> >
>
> ------------------------------
>
> Message: 6
> Date: Wed, 09 Jul 2014 09:54:04 +0200
> From: tommaso <tommasodb at googlemail.com>
> To: postgis-users at lists.osgeo.org
> Subject: Re: [postgis-users] Finding records withing XX distance from
> a geometry in the same table
> Message-ID: <op.xipz8esn9xab12 at tommaso-laptop>
> Content-Type: text/plain; charset="utf-8"; Format="flowed";
> DelSp="yes"
>
> You could use DWithin:
> http://blog.cleverelephant.ca/2010_08_01_archive.html
> Because your geometries are in 4326, you can cast the them and the point
> to geography (without ST_transform) and then use DWithin:
>
> WHERE ST_DWithin(parcels.geom::geography, yourpoint::geography, 100)
>
> Regards Tommaso
>
>
>
> On Wed, 09 Jul 2014 09:25:59 +0200, Guido Lemoine
> <guido.lemoine at jrc.ec.europa.eu> wrote:
>
> >
> > Alexander,
> >
> >
> > You would want to check out ST_BUFFER, which is doing what you intend to
> > do.
> >
> >
> > Guido
> >
> >
> >
> > From: postgis-users-bounces at lists.osgeo.org
> > [mailto:postgis-users-bounces at lists.osgeo.org] On Behalf Of Alexander W.
> > Rolek
> > Sent: 09 July 2014 05:36
> > To: postgis-users at lists.osgeo.org; Jeffrey Johnson
> > Subject: [postgis-users] Finding records withing XX distance from a
> > geometry in the same table
> >
> >
> > I'm trying to query all the records within XX distance from a geometry.
> > I would like to be able to reference the subject >geometry via it's id,
> > and then find every geometry that is either:
> >
> >
> > A) within XX distance from a x/y coordinate of the geometry
> >
> > B) within XX distance from the edge of the geometry on all sides. (ideal)
> >
> >
> > What is the best way to accomplish this? My current query:
> >
> >
> > SELECT *
> >
> > FROM sdgis.parcels4326 AS parcels
> >
> > WHERE ST_Distance_Sphere(
> >
> > parcels.geom,
> >
> >
> ST_Transform(ST_SetSRID(ST_MakePoint('6255896.314288','1898596.929109'),2230),4326)
> >
> > ) < 100.0;
> >
> >
> > This takes a few minutes to run on a table with just over 1 million
> > records. I would like to swap out the ST_MakePoint, >with a multipolygon
> > from a record that is looked up via it's id.
> >
> > Any suggestions are greatly appreciated.
> >
> >
> > --Alexander W. Rolek
>
>
>
> --
> Using Opera's mail client: http://www.opera.com/mail/
> -------------- next part --------------
> An HTML attachment was scrubbed...
> URL: <
> http://lists.osgeo.org/pipermail/postgis-users/attachments/20140709/ec0ea3bb/attachment-0001.html
> >
>
> ------------------------------
>
> _______________________________________________
> postgis-users mailing list
> postgis-users at lists.osgeo.org
> http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users
>
> End of postgis-users Digest, Vol 149, Issue 6
> *********************************************
>
--
Alexander W. Rolek
303-829-9989
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20140709/6063879f/attachment.html>
More information about the postgis-users
mailing list