Re: [postgis-users] Where to find Postgis-3.x for Postgresql 15

[Richard Greenwood]

Adding the apt.postgresql.org repository will get you the newest versions
of PostgreSQL and PostGIS. There are instructions here:
https://www.postgresql.org/download/linux/ubuntu/

On Wed, Aug 2, 2023 at 1:17 AM Bo Guo  wrote:

> Hi there,
>
> I was trying to upgrade Postgres 14 / Pistgis 3 to Postgres 15, but I
> could not find Postgis for Postgres 15. My operating system is Ubuntu 22.04.
>
> Thanks!
>
> *Bo Guo*
> 2820 S Alma School Rd #18-671, Chandler, AZ 85286
> p 480-656-9962 c 602-570-4697
>
> ___
> postgis-users mailing list
> postgis-users@lists.osgeo.org
> https://lists.osgeo.org/mailman/listinfo/postgis-users
>


-- 
Richard W. Greenwood
www.greenwoodmap.com
___
postgis-users mailing list
postgis-users@lists.osgeo.org
https://lists.osgeo.org/mailman/listinfo/postgis-users

Re: [postgis-users] Check and validate directionality of road lines

[Richard Greenwood]

First you would have to define what valid directionality is.

In some cases valid directionality might be that all roads project outward
in a cardinal direction from an origin. For example, city blocks running
north-south or east-west. In that case you could compare the X or Y
coordinates of the start and end points of a linestring to verify that they
were larger or smaller as appropriate. e.g. that a north bound road's end
point Y was greater than its starting Y. Obviously your data would have to
have an attribute that identified if a road was designated north-south or
east-west.

Another definition of a valid road direction is that it is always radiating
out like branches on a tree. This is more common in rural addressing
systems. If that's your definition of "valid" then you need to create nodes
at the forks, compare these to your linestring starting points and verify
that a fork node is not at the end of a linestring.

I'm sure there are other definitions of valid directionality, these are
just two that I have come across in my work.

Rich


On Tue, Apr 25, 2023 at 4:10 AM Shaozhong SHI 
wrote:

> How best to check and validate directionality of road lines?
>
> Regards,
>
> David
> ___
> postgis-users mailing list
> postgis-users@lists.osgeo.org
> https://lists.osgeo.org/mailman/listinfo/postgis-users
>


-- 
Richard W. Greenwood
www.greenwoodmap.com
___
postgis-users mailing list
postgis-users@lists.osgeo.org
https://lists.osgeo.org/mailman/listinfo/postgis-users

Re: [postgis-users] ogr-fdw for Postgres 15 Ubuntu 22.04

[Richard Greenwood]

I received the reply below from the pgsql-pkg-deb...@lists.postgresql.org
list and install postgresql-15-ogr-fdw is now available in the pgdg repo.




*Hi,the previous versions had not supported PG15 yet. This has been
fixednow and the package will be available over the next hour.*

On Wed, Oct 19, 2022 at 6:00 PM Richard Greenwood <
richard.greenw...@gmail.com> wrote:

> Thanks Regina. I did post on the pgsql-pkg-deb...@lists.postgresql.org list
> a couple of days ago but haven't heard anything yet.
>
> On Wed, Oct 19, 2022, 5:22 PM Regina Obe  wrote:
>
>> FWIW, I didn’t see it either.
>>
>> I don't think I saw it in yum either.  Might be related to issue of PG15
>> not regressing because of pg-regress change in PG 15
>> Which is fixed in
>> https://github.com/pramsey/pgsql-ogr-fdw/releases/tag/v1.1.3
>>
>> I have PostgreSQL 15 installed via Apt on Ubuntu 22.04
>>
>> And this is what I get for
>>
>> apt search ogr-fdw
>>
>> I'll check with the packagers and see if that is the issue.
>>
>> postgresql-10-ogr-fdw/jammy-pgdg 1.1.2-1.pgdg22.04+1 amd64
>>   PostgreSQL foreign data wrapper for OGR
>>
>> postgresql-10-ogr-fdw-dbgsym/jammy-pgdg 1.1.2-1.pgdg22.04+1 amd64
>>   debug symbols for postgresql-10-ogr-fdw
>>
>> postgresql-11-ogr-fdw/jammy-pgdg 1.1.2-1.pgdg22.04+1 amd64
>>   PostgreSQL foreign data wrapper for OGR
>>
>> postgresql-11-ogr-fdw-dbgsym/jammy-pgdg 1.1.2-1.pgdg22.04+1 amd64
>>   debug symbols for postgresql-11-ogr-fdw
>>
>> postgresql-12-ogr-fdw/jammy-pgdg 1.1.2-1.pgdg22.04+1 amd64
>>   PostgreSQL foreign data wrapper for OGR
>>
>> postgresql-12-ogr-fdw-dbgsym/jammy-pgdg 1.1.2-1.pgdg22.04+1 amd64
>>   debug symbols for postgresql-12-ogr-fdw
>>
>> postgresql-13-ogr-fdw/jammy-pgdg 1.1.2-1.pgdg22.04+1 amd64
>>   PostgreSQL foreign data wrapper for OGR
>>
>> postgresql-13-ogr-fdw-dbgsym/jammy-pgdg 1.1.2-1.pgdg22.04+1 amd64
>>   debug symbols for postgresql-13-ogr-fdw
>>
>> postgresql-14-ogr-fdw/jammy-pgdg 1.1.2-1.pgdg22.04+1 amd64
>>   PostgreSQL foreign data wrapper for OGR
>>
>> postgresql-14-ogr-fdw-dbgsym/jammy-pgdg 1.1.2-1.pgdg22.04+1 amd64
>>   debug symbols for postgresql-14-ogr-fdw
>>
>>
>> From: postgis-users [mailto:postgis-users-boun...@lists.osgeo.org] On
>> Behalf Of Richard Greenwood
>> Sent: Monday, October 17, 2022 8:54 AM
>> To: PostGIS Users Discussion 
>> Subject: [postgis-users] ogr-fdw for Postgres 15 Ubuntu 22.04
>>
>> I use the pgdg apt repo http://apt.postgresql.org/pub/repos/apt but it
>> doesn't have postgresql-15-ogr-fdw. Is this a deliberate change or maybe an
>> oversight? Should I be looking somewhere else?
>>
>> Thanks,
>> Rich
>>
>>
>> --
>> Richard W. Greenwood
>> www.greenwoodmap.com
>>
>> ___
>> postgis-users mailing list
>> postgis-users@lists.osgeo.org
>> https://lists.osgeo.org/mailman/listinfo/postgis-users
>>
>

-- 
Richard W. Greenwood
www.greenwoodmap.com
___
postgis-users mailing list
postgis-users@lists.osgeo.org
https://lists.osgeo.org/mailman/listinfo/postgis-users

Re: [postgis-users] ogr-fdw for Postgres 15 Ubuntu 22.04

[Richard Greenwood]

Thanks Regina. I did post on the pgsql-pkg-deb...@lists.postgresql.org list
a couple of days ago but haven't heard anything yet.

On Wed, Oct 19, 2022, 5:22 PM Regina Obe  wrote:

> FWIW, I didn’t see it either.
>
> I don't think I saw it in yum either.  Might be related to issue of PG15
> not regressing because of pg-regress change in PG 15
> Which is fixed in
> https://github.com/pramsey/pgsql-ogr-fdw/releases/tag/v1.1.3
>
> I have PostgreSQL 15 installed via Apt on Ubuntu 22.04
>
> And this is what I get for
>
> apt search ogr-fdw
>
> I'll check with the packagers and see if that is the issue.
>
> postgresql-10-ogr-fdw/jammy-pgdg 1.1.2-1.pgdg22.04+1 amd64
>   PostgreSQL foreign data wrapper for OGR
>
> postgresql-10-ogr-fdw-dbgsym/jammy-pgdg 1.1.2-1.pgdg22.04+1 amd64
>   debug symbols for postgresql-10-ogr-fdw
>
> postgresql-11-ogr-fdw/jammy-pgdg 1.1.2-1.pgdg22.04+1 amd64
>   PostgreSQL foreign data wrapper for OGR
>
> postgresql-11-ogr-fdw-dbgsym/jammy-pgdg 1.1.2-1.pgdg22.04+1 amd64
>   debug symbols for postgresql-11-ogr-fdw
>
> postgresql-12-ogr-fdw/jammy-pgdg 1.1.2-1.pgdg22.04+1 amd64
>   PostgreSQL foreign data wrapper for OGR
>
> postgresql-12-ogr-fdw-dbgsym/jammy-pgdg 1.1.2-1.pgdg22.04+1 amd64
>   debug symbols for postgresql-12-ogr-fdw
>
> postgresql-13-ogr-fdw/jammy-pgdg 1.1.2-1.pgdg22.04+1 amd64
>   PostgreSQL foreign data wrapper for OGR
>
> postgresql-13-ogr-fdw-dbgsym/jammy-pgdg 1.1.2-1.pgdg22.04+1 amd64
>   debug symbols for postgresql-13-ogr-fdw
>
> postgresql-14-ogr-fdw/jammy-pgdg 1.1.2-1.pgdg22.04+1 amd64
>   PostgreSQL foreign data wrapper for OGR
>
> postgresql-14-ogr-fdw-dbgsym/jammy-pgdg 1.1.2-1.pgdg22.04+1 amd64
>   debug symbols for postgresql-14-ogr-fdw
>
>
> From: postgis-users [mailto:postgis-users-boun...@lists.osgeo.org] On
> Behalf Of Richard Greenwood
> Sent: Monday, October 17, 2022 8:54 AM
> To: PostGIS Users Discussion 
> Subject: [postgis-users] ogr-fdw for Postgres 15 Ubuntu 22.04
>
> I use the pgdg apt repo http://apt.postgresql.org/pub/repos/apt but it
> doesn't have postgresql-15-ogr-fdw. Is this a deliberate change or maybe an
> oversight? Should I be looking somewhere else?
>
> Thanks,
> Rich
>
>
> --
> Richard W. Greenwood
> www.greenwoodmap.com
>
> ___
> postgis-users mailing list
> postgis-users@lists.osgeo.org
> https://lists.osgeo.org/mailman/listinfo/postgis-users
>
___
postgis-users mailing list
postgis-users@lists.osgeo.org
https://lists.osgeo.org/mailman/listinfo/postgis-users

Re: [postgis-users] ogr-fdw for Postgres 15 Ubuntu 22.04

[Richard Greenwood]

That's odd. I installed Postgres 15 + PostGIS 3.3.1 via the apt command
last Friday. Postgres version:
   PostgreSQL 15.0 (Ubuntu 15.0-1.pgdg22.04+1) on x86_64-pc-linux-gnu,
compiled by gcc (Ubuntu 11.2.0-19ubuntu1) 11.2.0, 64-bit
PostGIS full version:
   POSTGIS="3.3.1 3786b21" [EXTENSION] PGSQL="150"
GEOS="3.10.2-CAPI-1.16.0" PROJ="8.2.1" LIBXML="2.9.13" LIBJSON="0.15"
LIBPROTOBUF="1.3.3" WAGYU="0.5.0 (Internal)"


On Mon, Oct 17, 2022 at 10:05 AM Marco Boeringa 
wrote:

> The Synaptic package manager on Ubuntu 22.04.1 LTS with
> "http://apt.postgresql.org/; set for Jammy, doesn't yet list PostgreSQL
> 15 as available, I only see PG 13.8-1 and 14.5-2, so I guess we still
> need to wait a little for a package manager to release it?
>
> Op 17-10-2022 om 17:39 schreef Paul Ramsey:
> > Best to note to the packagers. I haven't heard any word about it being
> > deliberately dropped, but...?
> >
> > On Mon, Oct 17, 2022 at 5:54 AM Richard Greenwood
> >  wrote:
> >> I use the pgdg apt repo http://apt.postgresql.org/pub/repos/apt but it
> doesn't have postgresql-15-ogr-fdw. Is this a deliberate change or maybe an
> oversight? Should I be looking somewhere else?
> >>
> >> Thanks,
> >> Rich
> >>
> >> --
> >> Richard W. Greenwood
> >> www.greenwoodmap.com
> >> ___
> >> postgis-users mailing list
> >> postgis-users@lists.osgeo.org
> >> https://lists.osgeo.org/mailman/listinfo/postgis-users
> > ___
> > postgis-users mailing list
> > postgis-users@lists.osgeo.org
> > https://lists.osgeo.org/mailman/listinfo/postgis-users
> ___
> postgis-users mailing list
> postgis-users@lists.osgeo.org
> https://lists.osgeo.org/mailman/listinfo/postgis-users
>


-- 
Richard W. Greenwood
www.greenwoodmap.com
___
postgis-users mailing list
postgis-users@lists.osgeo.org
https://lists.osgeo.org/mailman/listinfo/postgis-users

[postgis-users] ogr-fdw for Postgres 15 Ubuntu 22.04

[Richard Greenwood]

I use the pgdg apt repo http://apt.postgresql.org/pub/repos/apt but it
doesn't have postgresql-15-ogr-fdw. Is this a deliberate change or maybe an
oversight? Should I be looking somewhere else?

Thanks,
Rich

-- 
Richard W. Greenwood
www.greenwoodmap.com
___
postgis-users mailing list
postgis-users@lists.osgeo.org
https://lists.osgeo.org/mailman/listinfo/postgis-users

Re: [postgis-users] snapping a table to itself

[Richard Greenwood]

Thanks for the suggestions. I was starting down the path you suggest of
creating nodes from the polygons and the ST_ConstranedDelaunayTriangles()
but I decided to post my question here first in case I was missing some
existing PostGIS goodness.

I couldn't get pprepair to build on Ubuntu 20.04. Possibly too new of a
cgal version:
  717 |   void remove_constraint(Constraint_id cid)
/usr/include/CGAL/Constrained_triangulation_plus_2.h:717:8: note:
candidate expects 1 argument, 2 provided

Thanks,
Rich


On Wed, Oct 27, 2021 at 11:35 AM Darafei "Komяpa" Praliaskouski <
m...@komzpa.net> wrote:

> Hey,
>
> In the past I've used https://github.com/tudelft3d/pprepair for this kind
> of data. Nowadays you can node the linework, take the
> ConstranedDelaunayTriangles of it and reassign all of the triangles to
> closest polygons to re-union back to simulate it.
>
> On Wed, Oct 27, 2021 at 8:15 PM Richard Greenwood <
> richard.greenw...@gmail.com> wrote:
>
>> I would like to automate the cleanup of a vector later. Neither ST_Snap()
>> nor ST_SnapToGrid() are doing it for me. I've got a little sketch below in
>> which I've attempted to illustrate my problem with ST_SnapToGrid(). The
>> green lines represent the vector layer that I'd like to clean up. I'd like
>> the four vertices to be snapped together. The red lines represent the grid.
>> So in this case the four vertices all fall in different grid cells and no
>> snapping occurs despite the vertices being closer together than the grid
>> size. The data is irregular so setting an origin will not be of much use.
>> Any suggestions?
>> [image: image.png]
>> --
>> Richard W. Greenwood
>> www.greenwoodmap.com
>> ___
>> postgis-users mailing list
>> postgis-users@lists.osgeo.org
>> https://lists.osgeo.org/mailman/listinfo/postgis-users
>>
> ___
> postgis-users mailing list
> postgis-users@lists.osgeo.org
> https://lists.osgeo.org/mailman/listinfo/postgis-users
>


-- 
Richard W. Greenwood
www.greenwoodmap.com
___
postgis-users mailing list
postgis-users@lists.osgeo.org
https://lists.osgeo.org/mailman/listinfo/postgis-users

[postgis-users] snapping a table to itself

[Richard Greenwood]

I would like to automate the cleanup of a vector later. Neither ST_Snap()
nor ST_SnapToGrid() are doing it for me. I've got a little sketch below in
which I've attempted to illustrate my problem with ST_SnapToGrid(). The
green lines represent the vector layer that I'd like to clean up. I'd like
the four vertices to be snapped together. The red lines represent the grid.
So in this case the four vertices all fall in different grid cells and no
snapping occurs despite the vertices being closer together than the grid
size. The data is irregular so setting an origin will not be of much use.
Any suggestions?
[image: image.png]
-- 
Richard W. Greenwood
www.greenwoodmap.com
___
postgis-users mailing list
postgis-users@lists.osgeo.org
https://lists.osgeo.org/mailman/listinfo/postgis-users

Re: [postgis-users] PostgreSQL 13, PostGIS 3.1 import - could not form projection

[Richard Greenwood]

On Tue, Oct 19, 2021 at 1:34 PM karsten  wrote:

> Is anyone out there that happens to know
> how can I make PostgreSQL use the new proj lib version I have installed?
>

I imagine that you would need to build postgis from source to get it to use
the newer proj version (and install the corresponding libproj-dev,
libgeos-dev, etc.). But before you go to that trouble you should explore
other options. Why not just comment out the offending line and put the
index in later? I did a quick test creating a table with
geometry(MultiPolygon,2263) and then
   CREATE INDEX test_geohash ON test USING btree
(st_geohash(st_transform(wkb_geometry, 4326)));
and go no errors. I'm on Ubuntu 20.02, postgres 13, postgis 3.1 installed
from the postgresql.org repo (not the native Ubuntu repo).

Try firing up a clean AWS instance and starting clean. I can't imagine that
you really need proj > 6.

Rich




> -Original Message-
> From: karsten [mailto:kars...@terragis.net]
> Sent: Friday, October 15, 2021 12:03
> To: 'PostGIS Users Discussion'
> Subject: RE: [postgis-users] PostgreSQL 13, PostGIS 3.1 import - could not
> form projection
>
> I installed  proj-bin v 7.2.1-1 that was available from the apt packages
> but
> in postgres (also after) restart of the db it still list PROJ="6.3.1" being
> used.
>
> How can I make postgresql use the new proj ?
>
>
> -Original Message-
> From: postgis-users [mailto:postgis-users-boun...@lists.osgeo.org] On
> Behalf
> Of karsten
> Sent: Friday, October 15, 2021 11:47
> To: 'PostGIS Users Discussion'
> Subject: Re: [postgis-users] PostgreSQL 13, PostGIS 3.1 import - could not
> form projection
>
> Yes will try to get this to work and report back here.
>
> The stack exchange issue mentioned that it worked with an older gdal
> version
>
> so will try with both different proj and gdal combinations
>
> Karsten
>
> -Original Message-
> From: postgis-users [mailto:postgis-users-boun...@lists.osgeo.org] On
> Behalf
> Of Paul Ramsey
> Sent: Friday, October 15, 2021 11:36
> To: PostGIS Users Discussion
> Subject: Re: [postgis-users] PostgreSQL 13, PostGIS 3.1 import - could not
> form projection
>
> There's something very particular about your environment and I don't know
> what it is. Try upgrading your proj version, and jimmying with other
> version
> combinations. There's nothing special about the SRID numbers, they work
> fine
> for me locally. I can't invest the time in setting up a mirror system that
> matches yours. If there's something specific about proj 6.3.1 maybe? (both
> you and the stack exchange issue)
>
> P
>
> > On Oct 15, 2021, at 11:11 AM, karsten  wrote:
> >
> > Hi All,
> >
> > on new server with Ubuntu 20.04 , PostgreSQL 13, PostGIS 3.1 I am
> > trying to import a plain sql dump . This mostly worked but when the
> > script is executing the creation of a geohash (involving a
> > transformation from srid 2263 to geographic 4326) like this
> >
> > CREATE INDEX parcels_geohash ON parcels USING btree
> > (st_geohash(st_transform(geom, 4326)));
> >
> > I am getting this error:
> > could not form projection (PJ) from 'srid=2263' to 'srid=4326'
> >
> > How can I fix this ?
> >
> > Here are more details about the environment:
> > postgres: PostgreSQL 13.4 (Ubuntu 13.4-4.pgdg20.04+1) on
> > x86_64-pc-linux-gnu, compiled by gcc (Ubuntu 9.3.0-17ubuntu1~20.04)
> > 9.3.0, 64-bit
> > postgis_version: 3.1 USE_GEOS=1 USE_PROJ=1 USE_STATS=1
> > postgis_proj_version: 6.3.1
> >
> > Cheers
> > Karsten
> >
> > Karsten Vennemann
> > www.terragis.net
> >
> > ___
> > postgis-users mailing list
> > postgis-users@lists.osgeo.org
> > https://lists.osgeo.org/mailman/listinfo/postgis-users
>
> ___
> postgis-users mailing list
> postgis-users@lists.osgeo.org
> https://lists.osgeo.org/mailman/listinfo/postgis-users
>
>
> ___
> postgis-users mailing list
> postgis-users@lists.osgeo.org
> https://lists.osgeo.org/mailman/listinfo/postgis-users
>
>
> ___
> postgis-users mailing list
> postgis-users@lists.osgeo.org
> https://lists.osgeo.org/mailman/listinfo/postgis-users
>


-- 
Richard W. Greenwood
www.greenwoodmap.com
___
postgis-users mailing list
postgis-users@lists.osgeo.org
https://lists.osgeo.org/mailman/listinfo/postgis-users

Re: [postgis-users] ownership and permissions on ogr_fdw shapefiles

[Richard Greenwood]

Thanks Paul! It all lives in a shell script so it's not a big deal to reset
the ownership & permissions. But I didn't want to miss out on any fdw
goodness if there was a more elegant way of doing it. I hate looking at my
old scripts and saying "geez, I didn't know very much back then!"

On Tue, Jul 13, 2021 at 3:31 PM Paul Ramsey 
wrote:

> ogr must be removing and re-writing the files, so you're getting the
> ownership of the writing process, which is the postgres daemon. No way to
> change that (except running your daemon under the ownership you want!!
> (don't)). A cron job may have to suffice.
>
> P
>
> > On Jul 13, 2021, at 2:26 PM, Richard Greenwood <
> richard.greenw...@gmail.com> wrote:
> >
> > I'm using ogr_fdw to delete and insert rows in shapefiles from postgis
> tables. The operations change the ownership and permissions of the files.
> Is it possible to control ownership and permissions with ogr_fdw or do I
> need to do that outside of psql. For example, before my sql operations on
> the ogr_fdw tables they look like:
> > -rw-rw 1 rich postgres 465K Jul 13 15:08 GUERNSEY_PARCELS.dbf
> > -rw-rw 1 rich postgres  96K Jul 13 15:08 GUERNSEY_PARCELS.shp
> > -rw-rw 1 rich postgres 5.2K Jul 13 15:08 GUERNSEY_PARCELS.shx
> > and after:
> > -rw--- 1 postgres postgres 465K Jul 13 15:18 GUERNSEY_PARCELS.dbf
> > -rw--- 1 postgres postgres  96K Jul 13 15:18 GUERNSEY_PARCELS.shp
> > -rw--- 1 postgres postgres 5.2K Jul 13 15:18 GUERNSEY_PARCELS.shx
> > I'd like the ownership & permissions to not change.
> >
> > Thanks,
> > Rich
> > --
> > Richard W. Greenwood, PLS
> > www.greenwoodmap.com
> > ___
> > postgis-users mailing list
> > postgis-users@lists.osgeo.org
> > https://lists.osgeo.org/mailman/listinfo/postgis-users
>
> ___
> postgis-users mailing list
> postgis-users@lists.osgeo.org
> https://lists.osgeo.org/mailman/listinfo/postgis-users
>


-- 
Richard W. Greenwood, PLS
www.greenwoodmap.com
___
postgis-users mailing list
postgis-users@lists.osgeo.org
https://lists.osgeo.org/mailman/listinfo/postgis-users

[postgis-users] ownership and permissions on ogr_fdw shapefiles

[Richard Greenwood]

I'm using ogr_fdw to delete and insert rows in shapefiles from postgis
tables. The operations change the ownership and permissions of the files.
Is it possible to control ownership and permissions with ogr_fdw or do I
need to do that outside of psql. For example, before my sql operations on
the ogr_fdw tables they look like:
-rw-rw 1 rich postgres 465K Jul 13 15:08 GUERNSEY_PARCELS.dbf
-rw-rw 1 rich postgres  96K Jul 13 15:08 GUERNSEY_PARCELS.shp
-rw-rw 1 rich postgres 5.2K Jul 13 15:08 GUERNSEY_PARCELS.shx
and after:
-rw--- 1 postgres postgres 465K Jul 13 15:18 GUERNSEY_PARCELS.dbf
-rw--- 1 postgres postgres  96K Jul 13 15:18 GUERNSEY_PARCELS.shp
-rw--- 1 postgres postgres 5.2K Jul 13 15:18 GUERNSEY_PARCELS.shx
I'd like the ownership & permissions to not change.

Thanks,
Rich
-- 
Richard W. Greenwood, PLS
www.greenwoodmap.com
___
postgis-users mailing list
postgis-users@lists.osgeo.org
https://lists.osgeo.org/mailman/listinfo/postgis-users

Re: [postgis-users] What is SRID 999155

[Richard Greenwood]

On Fri, Oct 2, 2020 at 5:22 AM Greg Troxel  wrote:

>
> Simon G Greener  writes:
>
> >
> > PostGIS references SRID 999155.
>

If you use ogr2ogr to import a file of unknown coordinate system postgis
will add a row to your spatial_ref_sys table with a srid one higher than
the highest number in the table. Might that be a possibility in your case?


> That feels like a bug.  File and line number?
>
> > What is it? It is not listed on reference.org.
>
> I find no trace of it.
> ___
> postgis-users mailing list
> postgis-users@lists.osgeo.org
> https://lists.osgeo.org/mailman/listinfo/postgis-users



-- 
Richard W. Greenwood, PLS
www.greenwoodmap.com
___
postgis-users mailing list
postgis-users@lists.osgeo.org
https://lists.osgeo.org/mailman/listinfo/postgis-users

Re: [postgis-users] Ubuntu upgrade conflict

[Richard Greenwood]

On Sun, Feb 2, 2020 at 8:06 PM Regina Obe  wrote:

> I think apt upgrade tries to upgrade everything unless you mark some
> things as hold back.
>
>
>
> Which version of PostgreSQL/PostGIS are you running.  From looks of it,
> looks like you have both PostgreSQL 11 2.5  and PostgreSQL 11 3.0
>

I had installed postgres 11 and postgis 2.5 on a new AWS Ubuntu instance
about a year and a half ago and have done normal upgrades since. Below is
what I have now:

liblwgeom-2.5-0/bionic-pgdg,now 2.5.3+dfsg-3.pgdg18.04+1 amd64
[installed,automatic]
libsfcgal1/bionic-pgdg,now 1.3.5-5~pgdg18.04+1 amd64 [installed,automatic]
postgis/bionic-pgdg,now 3.0.0+dfsg-2~exp1.pgdg18.04+1 amd64 [installed]
postgis-doc/bionic-pgdg,now 3.0.0+dfsg-2~exp1.pgdg18.04+1 all
[installed,automatic]
postgresql-10-postgis-2.5-scripts/bionic-pgdg,now 2.5.3+dfsg-3.pgdg18.04+1
all [installed,automatic]
postgresql-11-postgis-2.5/bionic-pgdg,now 2.5.3+dfsg-3.pgdg18.04+1 amd64
[installed]
postgresql-11-postgis-3/bionic-pgdg,now 3.0.0+dfsg-2~exp1.pgdg18.04+1 amd64
[installed,automatic]
postgresql-11-postgis-3-scripts/bionic-pgdg,now
3.0.0+dfsg-2~exp1.pgdg18.04+1 all [installed,automatic]
postgresql-12-postgis-3/bionic-pgdg,now 3.0.0+dfsg-2~exp1.pgdg18.04+1 amd64
[installed,automatic]
postgresql-12-postgis-3-scripts/bionic-pgdg,now
3.0.0+dfsg-2~exp1.pgdg18.04+1 all [installed,automatic]

I assume the 'installed,automatic' is how I picked up postgis-3,
postgresql-12, etc.  I did 'apt remove postgresql-11-postgis-3-scripts
postgresql-11-postgis-3' and the problem is gone.

Between my laptop, desktop and server I have 3 pretty similar
ubuntu/postgres/postgis configurations but only the AWS one
had postgresql-11-postgis-3.

Thanks you for your help.

Rich






>  *From:* postgis-users [mailto:postgis-users-boun...@lists.osgeo.org] *On
> Behalf Of *Richard Greenwood
>
> *Sent:* Sunday, February 2, 2020 9:29 AM
> *To:* PostGIS Users Discussion 
> *Subject:* [postgis-users] Ubuntu upgrade conflict
>
>
>
> I am getting the error below when running 'apt upgrade' on a Ubuntu 18.04
> server. I'm not upgrading postgis versions or any, just basic system
> maintenance. Any ideas?
>
>
>
> (Reading database ... 156285 files and directories currently installed.)
> Preparing to unpack
> .../postgresql-11-postgis-2.5-scripts_2.5.3+dfsg-3.pgdg18.04+1_all.deb ...
> Unpacking postgresql-11-postgis-2.5-scripts (2.5.3+dfsg-3.pgdg18.04+1)
> over (2.5.3+dfsg-2.pgdg18.04+1) ...
> dpkg: error processing archive
> /var/cache/apt/archives/postgresql-11-postgis-2.5-scripts_2.5.3+dfsg-3.pgdg18.04+1_all.deb
> (--unpack):
>  trying to overwrite
> '/usr/share/postgresql/11/applications/shp2pgsql-gui.desktop', which is
> also in package postgresql-11-postgis-3-scripts
> 3.0.0+dfsg-2~exp1.pgdg18.04+1
> dpkg-deb: error: paste subprocess was killed by signal (Broken pipe)
> Errors were encountered while processing:
>
>  
> /var/cache/apt/archives/postgresql-11-postgis-2.5-scripts_2.5.3+dfsg-3.pgdg18.04+1_all.deb
> E: Sub-process /usr/bin/dpkg returned an error code (1)
>
>
>
> Thanks
>
> --
>
> Richard W. Greenwood, PLS
> www.greenwoodmap.com
> ___
> postgis-users mailing list
> postgis-users@lists.osgeo.org
> https://lists.osgeo.org/mailman/listinfo/postgis-users



-- 
Richard W. Greenwood, PLS
www.greenwoodmap.com
___
postgis-users mailing list
postgis-users@lists.osgeo.org
https://lists.osgeo.org/mailman/listinfo/postgis-users

Re: [postgis-users] Ubuntu upgrade conflict

[Richard Greenwood]

Thank you. I had not run into that before.

Rich


On Mon, Feb 3, 2020 at 2:45 AM Micha Silver  wrote:

> I can confirm a similar error on Debian 10.
> As Regina suggested I checked and found Postgresql-11-Postgis-3 packages
> installed also. After purging those, the routine update worked.
>
> On 2/3/2020 5:05 AM, Regina Obe wrote:
>
> I think apt upgrade tries to upgrade everything unless you mark some
> things as hold back.
>
>
>
> Which version of PostgreSQL/PostGIS are you running.  From looks of it,
> looks like you have both PostgreSQL 11 2.5  and PostgreSQL 11 3.0
>
>
>
> *From:* postgis-users [mailto:postgis-users-boun...@lists.osgeo.org
> ] *On Behalf Of *Richard Greenwood
> *Sent:* Sunday, February 2, 2020 9:29 AM
> *To:* PostGIS Users Discussion 
> 
> *Subject:* [postgis-users] Ubuntu upgrade conflict
>
>
>
> I am getting the error below when running 'apt upgrade' on a Ubuntu 18.04
> server. I'm not upgrading postgis versions or any, just basic system
> maintenance. Any ideas?
>
>
>
> (Reading database ... 156285 files and directories currently installed.)
> Preparing to unpack
> .../postgresql-11-postgis-2.5-scripts_2.5.3+dfsg-3.pgdg18.04+1_all.deb ...
> Unpacking postgresql-11-postgis-2.5-scripts (2.5.3+dfsg-3.pgdg18.04+1)
> over (2.5.3+dfsg-2.pgdg18.04+1) ...
> dpkg: error processing archive
> /var/cache/apt/archives/postgresql-11-postgis-2.5-scripts_2.5.3+dfsg-3.pgdg18.04+1_all.deb
> (--unpack):
>  trying to overwrite
> '/usr/share/postgresql/11/applications/shp2pgsql-gui.desktop', which is
> also in package postgresql-11-postgis-3-scripts
> 3.0.0+dfsg-2~exp1.pgdg18.04+1
> dpkg-deb: error: paste subprocess was killed by signal (Broken pipe)
> Errors were encountered while processing:
>
>  
> /var/cache/apt/archives/postgresql-11-postgis-2.5-scripts_2.5.3+dfsg-3.pgdg18.04+1_all.deb
> E: Sub-process /usr/bin/dpkg returned an error code (1)
>
>
>
> Thanks
>
> --
>
> Richard W. Greenwood, PLS
> www.greenwoodmap.com
>
> ___
> postgis-users mailing 
> listpostgis-users@lists.osgeo.orghttps://lists.osgeo.org/mailman/listinfo/postgis-users
>
>
> --
> Micha Silver
> Ben Gurion Univ.
> Sde Boker, Remote Sensing Lab
> +972-523-665918
>
> ___
> postgis-users mailing list
> postgis-users@lists.osgeo.org
> https://lists.osgeo.org/mailman/listinfo/postgis-users



-- 
Richard W. Greenwood, PLS
www.greenwoodmap.com
___
postgis-users mailing list
postgis-users@lists.osgeo.org
https://lists.osgeo.org/mailman/listinfo/postgis-users

[postgis-users] Ubuntu upgrade conflict

[Richard Greenwood]

I am getting the error below when running 'apt upgrade' on a Ubuntu 18.04
server. I'm not upgrading postgis versions or any, just basic system
maintenance. Any ideas?

(Reading database ... 156285 files and directories currently installed.)
Preparing to unpack
.../postgresql-11-postgis-2.5-scripts_2.5.3+dfsg-3.pgdg18.04+1_all.deb ...
Unpacking postgresql-11-postgis-2.5-scripts (2.5.3+dfsg-3.pgdg18.04+1) over
(2.5.3+dfsg-2.pgdg18.04+1) ...
dpkg: error processing archive
/var/cache/apt/archives/postgresql-11-postgis-2.5-scripts_2.5.3+dfsg-3.pgdg18.04+1_all.deb
(--unpack):
 trying to overwrite
'/usr/share/postgresql/11/applications/shp2pgsql-gui.desktop', which is
also in package postgresql-11-postgis-3-scripts
3.0.0+dfsg-2~exp1.pgdg18.04+1
dpkg-deb: error: paste subprocess was killed by signal (Broken pipe)
Errors were encountered while processing:
 
/var/cache/apt/archives/postgresql-11-postgis-2.5-scripts_2.5.3+dfsg-3.pgdg18.04+1_all.deb
E: Sub-process /usr/bin/dpkg returned an error code (1)

Thanks
-- 
Richard W. Greenwood, PLS
www.greenwoodmap.com
___
postgis-users mailing list
postgis-users@lists.osgeo.org
https://lists.osgeo.org/mailman/listinfo/postgis-users

Re: [postgis-users] QGIS + PostGIS in production environment

[Richard Greenwood]

On Thu, Jun 15, 2017 at 1:58 PM, Cap Diniz  wrote:

>
> We are using Dell PowerEdge-R430 [1] for the PostGIS server with OS Debian
> 8.7 on a 1gigabit network. For each topographic sheet we use one database,
> this means that we have hundreds of databases in production.
>

Why do you have some many different databases? I don't know what impact it
would have on performance, but it seems like it would me challenging to
manage.

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

Re: [postgis-users] identifing duplicate line features (with different draw directions0

[Richard Greenwood]

Have you thought about using a topology? Seems like that's sort of what
you're after.

On May 19, 2017 11:55 AM, "Basques, Bob (CI-StPaul)" <
bob.basq...@ci.stpaul.mn.us> wrote:

> I’m trying to remove all duplicated parcel edges from an exploded parcel
> dataset.  I have a set of 2 point line segments and I’ve been able to find
> the geom duplicates ok witha window partition, but the number was really
> low.  Upon examining the data more closely, I figured out that the majoirty
> of the duplicates are indeed the same line, but the end points are swapped
> around, so they appear in the window as different feature.
>
> Anyone have a SQL chunk/function that I should look at to get to this
> matching of spatial location?
>
> Thanks
>
> bobb
>
>
>
>
> "Be nice to people on your way up because you meet them on your way down."
>  - Jimmy Durante
>
>
>
>
> ___
> postgis-users mailing list
> postgis-users@lists.osgeo.org
> https://lists.osgeo.org/mailman/listinfo/postgis-users
>
___
postgis-users mailing list
postgis-users@lists.osgeo.org
https://lists.osgeo.org/mailman/listinfo/postgis-users

Re: [postgis-users] importing curved polygons

[Richard Greenwood]

Thank you Andreas. Summary: Using ogr2ogr 2.2 (which hasn't been released
yet) curves are read from an ESRI file gdb and written to postgis as
curves. When building ogr without any explicit configure option for fgdb
the OpenFileGDB driver is used. To use the ESRI driver the --with-fgdb
option has to be used but I personally didn't get that working. Using the
OpenFileGDB driver is probably preferable unless you need to write to a
file gdb. I was told on the gdal-dev list that the Windows dev version of
gdal available at gisinternals probably supports curves but I have not
tried it.

As always, thanks for the help.

Rich


On Wed, Mar 8, 2017 at 8:25 AM, Neumann, Andreas <a.neum...@carto.net>
wrote:

> Hi Rich,
>
> What GDAL/OGR version do you have? Which driver do you use? There are two.
>
> According to
>
> http://www.gdal.org/drv_openfilegdb.html
> and
> http://www.gdal.org/drv_filegdb.html (requires ESRI FileGDB API SDK)
> both versions require version 2.2 or higher.
>
> It is quite likely that you use an older version.
>
> Try "ogrinfo --version" to get the version.
>
> Andreas
>
> On 2017-03-08 16:16, Richard Greenwood wrote:
>
> I have received an ESRI file geodatabase with polygons containing curves
> that I would like to import into postgis. Using ogr2ogr the curved portion
> of the polygon is comprised of the chord of the curve (example below).
>
> Does anyone have any suggestions for importing an ESRI file geodatabase
> containing curved polygons?
>
> Thanks,
> Rich
>
>
>  MULTIPOLYGON(((-11750835.4823935 5060835.88854035,-11750867.8784639
> 5060835.62656567,-11750871.3924507 5060835.59816625,-11750871.8264507
> 5060873.75984909,-11750871.2171506 5060883.12929326,-11750870.900856
> 5060884.35132355,-11750864.5457614 5060884.41587281,-11750835.8933664
> 5060856.50810022,-11750835.4823935 5060835.88854035)))
>
> --
> Richard W. Greenwood, PLS
> www.greenwoodmap.com
>
> ___
> postgis-users mailing list
> postgis-users@lists.osgeo.org
> https://lists.osgeo.org/mailman/listinfo/postgis-users
>
>
>
>



-- 
Richard W. Greenwood, PLS
www.greenwoodmap.com
___
postgis-users mailing list
postgis-users@lists.osgeo.org
https://lists.osgeo.org/mailman/listinfo/postgis-users

[postgis-users] importing curved polygons

[Richard Greenwood]

I have received an ESRI file geodatabase with polygons containing curves
that I would like to import into postgis. Using ogr2ogr the curved portion
of the polygon is comprised of the chord of the curve (example below).

Does anyone have any suggestions for importing an ESRI file geodatabase
containing curved polygons?

Thanks,
Rich


 MULTIPOLYGON(((-11750835.4823935 5060835.88854035,-11750867.8784639
5060835.62656567,-11750871.3924507 5060835.59816625,-11750871.8264507
5060873.75984909,-11750871.2171506 5060883.12929326,-11750870.900856
5060884.35132355,-11750864.5457614 5060884.41587281,-11750835.8933664
5060856.50810022,-11750835.4823935 5060835.88854035)))

-- 
Richard W. Greenwood, PLS
www.greenwoodmap.com
___
postgis-users mailing list
postgis-users@lists.osgeo.org
https://lists.osgeo.org/mailman/listinfo/postgis-users

Re: [postgis-users] Creating an 'editable' view in PostGIS

[Richard Greenwood]

Why not just have two geometry columns in one table? One column for the
actual geometry and another for the label.

On Fri, Sep 16, 2016 at 2:36 PM, Matthew Baker  wrote:

> Hi all,
>
> I've asked this on GIS.SE, to no avail:
>
> I'd like to store label placement properties in a separate table, build a
> view of the data I want to use for my map, and use QGIS to move the labels
> manually. The idea is when the labels are updated, the placement
> coordinates would go into the LABEL_X and LABEL_Y fields in the label
> placement table, but the geometry of the underlying points and several
> other attributes (coming from the source tables) would remain unchanged.
>
> However, when all is in place, QGIS throws the following error when trying
> to save the edits to the view (the edit session can start, labels moved,
> but cannot save):
>
> Could not commit changes to layer schools_district_map
>
> Errors: ERROR: 1 attribute value change(s) not applied.
>
> Provider errors: PostGIS error while changing attributes:
>
> ERROR:  infinite recursion detected in rules for relation 
> "schools_district_map"
>
>
> Here is the definition of the view :
>
> CREATE OR REPLACE VIEW public.schools_district_map AS
>  SELECT sch.schnum,
> sch.oid,
> sch.abbreviation,
> sch.school_level,
> sch.geom,
> l.label_x,
> l.label_y
>FROM temp_schools_label sch
>  LEFT JOIN district_map_labels l ON sch.schnum = l.schnum;
>
> And here are the rules I've applied to make the view 'editable':
>
> --delete rulecreate or replace rule "delete_label" as on delete to 
> schools_district_map do insteaddelete from schools_district_map where oid = 
> old.oid;
> --insert rule
> create or replace rule "insert_label" as on insert to schools_district_map do 
> insteadinsert into schools_district_map (label_x, label_y)values 
> (new.label_x, new.label_y);
>
> --update rulecreate or replace rule "labels_update" as on UPDATE TO 
> schools_district_map do instead update schools_district_map set
> label_x = new.label_x, label_y = new.label_ywhere oid = new.oid;
>
> QGIS is then set to display the labels using the label_x and label_y field.
>
> I used this post as a guide to build the view, rules:
>
> http://gis.stackexchange.com/questions/88120/how-to-set-
> posgis-default-sequential-value-in-a-qgis-editable-view
>
> If anyone can spot where I might have left something out, or if there is a
> glaring oversight on my part, OR if this is maybe a bad idea... let me know!
>
> Thank you!!!
>
> -Matt Baker
> Denver Public Schools
> Denver, CO
>
>
>
> ___
> postgis-users mailing list
> postgis-users@lists.osgeo.org
> http://lists.osgeo.org/mailman/listinfo/postgis-users
>



-- 
Richard W. Greenwood, PLS
www.greenwoodmap.com
___
postgis-users mailing list
postgis-users@lists.osgeo.org
http://lists.osgeo.org/mailman/listinfo/postgis-users

[postgis-users] Raster - Access to offline bands disabled

[Richard Greenwood]

I'm running postgres 9.4 with postgis 2.1 on Ubuntu 14.04 and get the error
"Access to offline bands disabled"
In /etc/postgresql/9.4/main/environment I have set
POSTGIS_ENABLE_OUTDB_RASTERS=1
POSTGIS_GDAL_ENABLED_DRIVERS=ENABLE_ALL
and restarted the server (pg_ctl -m fast restart)

I also tried
SET postgis.enable_outdb_rasters = True;
SET postgis.gdal_enabled_drivers = 'ENABLE_ALL';
Although I realize that is the postgis 2.2 way.

Any suggestions as to where else I should look or how else to set the
server environment?

Thanks,
Rich

-- 
Richard W. Greenwood, PLS
www.greenwoodmap.com
___
postgis-users mailing list
postgis-users@lists.osgeo.org
http://lists.osgeo.org/mailman/listinfo/postgis-users

Re: [postgis-users] prj2esri.org

[Richard Greenwood]

Clifford,

This probably isn't really what you're looking for, but it's another
creative take on projection guessing:
  http://projfinder.com/

Rich


On Tue, Jan 12, 2016 at 4:28 PM, Clifford Snow 
wrote:

>
> On Tue, Jan 12, 2016 at 1:17 AM, Andrea Aime  > wrote:
>
>> 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").
>>
>
> Thanks for the explanation. The process on the surface seems
>> straightforward enough. I was expecting that a tool like proj would be used
>> to normalize the file. This actually makes great sense now that I think
>> about it.
>
>
> Thanks,
> Clifford
>
>
> --
> @osm_seattle
> osm_seattle.snowandsnow.us
> OpenStreetMap: Maps with a human touch
>
> ___
> postgis-users mailing list
> postgis-users@lists.osgeo.org
> http://lists.osgeo.org/mailman/listinfo/postgis-users
>



-- 
Richard W. Greenwood, PLS
www.greenwoodmap.com
___
postgis-users mailing list
postgis-users@lists.osgeo.org
http://lists.osgeo.org/mailman/listinfo/postgis-users

Re: [postgis-users] restore problem

[Richard Greenwood]

Darrel,

A couple people have already pointed out that creating the postgis
extension in the target database before you restore is advisable. Going
along with that I also recommend excluding postgis from your backup.
Postgis is put in the "public" schema so I avoid putting anything else in
the "public" schema and then exclude it from my backups. The backups are a
little smaller, there are fewer notices when you restore, and upgrading the
postgis versions is a breeze.

Best of luck,
Rich


On Wed, Nov 11, 2015 at 2:56 PM, Darrel Maddy 
wrote:

> Dear Regina,
>
>
>
> Many thanks for this. A least now it looks like this is nothing I have
> done (my first assumption with all problems is that I did something wrong –
> this assumption is usually correct).
>
>
>
> I had seen that ticket (this was the chatter I referred to) but assumed
> with the Milestone set at 2.0.8  I was hoping this had been fixed by 2.2 –
> clearly I do not understand this terminology!
>
>
>
> I will attempt to use this workaround tomorrow – hopefully it will resolve
> the restore issue and I will be back tomorrow full of praise J
>
>
>
> Santa is bringing me a copy of your “PostrgreSQL: Up and Running book”, so
> hopefully once I am good to go with the basics I can really start to see
> what this combo can do.
>
>
>
> Best wishes
>
>
>
> Darrel
>
>
>
>
>
>
>
> *From:* postgis-users [mailto:postgis-users-boun...@lists.osgeo.org] *On
> Behalf Of *Paragon Corporation
> *Sent:* 11 November 2015 21:43
>
> *To:* 'PostGIS Users Discussion' 
> *Subject:* Re: [postgis-users] restore problem
>
>
>
> Darrel,
>
>
>
> Sadly you are not the only one.
>
>
>
> I have the issue documented here:
>
>
>
> https://trac.osgeo.org/postgis/ticket/2485
>
>
>
> and I haven't come up with a generic enough fix I feel comfortable
> including in PostGIS code.
>
>
>
> The work-around is to do this
>
>
>
> 1)  Create your database and do  CREATE EXTENSION postgis;
>
> 2)  Then add search paths to the functions used in check constraints
>
> raster_constraint_info_regular_blocking
>
> _raster_constraint_nodata_values
>
> _raster_constraint_out_db
>
>
>
> _raster_constraint_pixel_types
>
> e.g.
>
>
>
> ALTER FUNCTION _raster_constraint_pixel_types(raster)
>
>   SET search_path=pg_catalog,public,postgis;
>
>
>
>
>
> 3)  Then restore your data.
>
> I'll try to write a full script up in the ticket
>
>
>
> Hope that helps,
>
> Regina
>
>
>
>
>
> *From:* postgis-users [mailto:postgis-users-boun...@lists.osgeo.org
> ] *On Behalf Of *Darrel Maddy
> *Sent:* Wednesday, November 11, 2015 2:03 PM
> *To:* PostGIS Users Discussion 
> *Subject:* Re: [postgis-users] restore problem
>
>
>
> OK can I come at this from a different direction? Are there any backup
> options I should be selecting to eliminate this problem?
>
>
>
> I am really struggling to understand why I cannot backup and restore a
> raster table – surely others have had this issue?  At present while my
> databases are small (i.e. <13GB) I can live with rebuilding if things fall
> over but these tables will grow to TB sizes soon (if I deploy this for
> real) and I cannot contemplate having no reliable backup.
>
>
>
> I have been selecting only the default options for backup – should I be
> doing something differently?
>
>
>
> Could this be a problem with the raster tables themselves? They seem to
> work with my SQL queries (including putting the tiles together and viewing
> in QGIS) but is there anything I should be doing to them before I select
> backup? If the backup does not report errors does this mean there are no
> errors in the backup?
>
>
>
> I realise I have much to learn using postgres but I see little point in
> putting in that much effort if backup/restore does not work for me.
>
>
>
> I really do appreciate the helpful suggestions that have been made so far
> but are there any further suggestions?
>
>
>
> Darrel
>
>
>
>
>
>
>
>
>
>
>
>
>
> *From:* postgis-users [mailto:postgis-users-boun...@lists.osgeo.org
> ] *On Behalf Of *Darrel Maddy
> *Sent:* 10 November, 2015 10:25 PM
> *To:* PostGIS Users Discussion 
> *Subject:* Re: [postgis-users] restore problem
>
>
>
> The default appears to be custom. I did not change anything when I did
> this. I just accepted the defaults.
>
>
>
> Darrel
>
>
>
> *From:* postgis-users [mailto:postgis-users-boun...@lists.osgeo.org
> ] *On Behalf Of *Bborie Park
> *Sent:* 10 November 2015 22:20
> *To:* PostGIS Users Discussion 
> *Subject:* Re: [postgis-users] restore problem
>
>
>
> Odd. Looking at your error message again, it looks like the constraint is
> already in place by the time the data is getting loaded...
>
>
>
> What are you restoring from? Tar, custom or text?
>
>
>
> On Tue, Nov 10, 2015 at 

Re: [postgis-users] multiple postgis versions on a single instance

[Richard Greenwood]

Thanks Regina, that all makes complete sense.

Rich

On Fri, Oct 9, 2015 at 8:53 PM, Paragon Corporation  wrote:

> Rich,
>
> > I'm anxious to try postgis 2.2 (Visvalingam-Whyatt being the carrot for
> me).  From my Googling I'm under the impression that I can access multiple
> versions of postgis from a single instance of postgres. Am I correct?
>
> Yes.  I run multiple versions on a single instance all the time.
>
>
>
>
>
> > When I build and install I don't think I need to change the prefix
> because things will go into 2.2 directories, correct?
>
> Correct.  The postgis.control file gets overwritten though, so last
> install becomes the default PostGIS install when you don't explicitly set a
> version and during database restore.
>
>
>
>
>
> > And if my above understanding is correct, then my last question is when
> I create a new database how will I specify the postgis 2.2 extension rather
> than the existing version?
>
>
>
> If PostGIS 2.2.0 is the last install you did, your new database would be
> 2.2.0 with
>
>
>
> CREATE EXTENSION postgis;
>
>
>
> If you don't want that, edit the postgis.control file in share/extension
> folder back to old version.
>
>
>
> To explicitly state the version of PostGIS you want, you would do:
>
>
>
> CREATE EXTENSION postgis VERSION "2.1.8";
>
>
>
> Replacing 2.1.8 with version you want.
>
>
>
>
>
>
>
> >  Sorry, I realize these are pretty noob questions from a guy that's
> been using postgis for a long time...
>
> No need to be sorry. Lots of things change in how to do things and it is
> hard to keep up with changes in both PostGIS and PostgreSQL for anybody.
>
>
>
> > Thanks,
>
> > Rich
>
>
>
> >--
>
> > Richard W. Greenwood, PLS
> > www.greenwoodmap.com
>
>
>
>
>
> Hope that helps,
>
> Regina
>
> http://www.postgis.us
>
> http://postgis.net
>
>
>
>
>
> ___
> postgis-users mailing list
> postgis-users@lists.osgeo.org
> http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users
>



-- 
Richard W. Greenwood, PLS
www.greenwoodmap.com
___
postgis-users mailing list
postgis-users@lists.osgeo.org
http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users

[postgis-users] multiple postgis versions on a single instance

[Richard Greenwood]

I'm anxious to try postgis 2.2 (Visvalingam-Whyatt being the carrot for
me).  From my Googling I'm under the impression that I can access multiple
versions of postgis from a single instance of postgres. Am I correct?

When I build and install I don't think I need to change the prefix because
things will go into 2.2 directories, correct?

And if my above understanding is correct, then my last question is when I
create a new database how will I specify the postgis 2.2 extension rather
than the existing version?

Sorry, I realize these are pretty noob questions from a guy that's been
using postgis for a long time...

Thanks,
Rich

-- 
Richard W. Greenwood, PLS
www.greenwoodmap.com
___
postgis-users mailing list
postgis-users@lists.osgeo.org
http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users

Re: [postgis-users] Different results in datum transform pg v2 vs pg v1

[Richard Greenwood]

You're going from NAD27 to NAD83. Do you have the nad27 and nad83 datum
files installed in /usr/share/proj?

Rich

On Fri, Jun 5, 2015 at 10:19 AM, David Fawcett david.fawc...@gmail.com
wrote:

 I forgot to include the full version info.

  SELECT Postgis_Full_Version();

 Old Server:
 POSTGIS=1.3.6 GEOS=3.2.2-CAPI-1.6.2 PROJ=Rel. 4.7.1, 23 September
 2009 USE_STATS

 New Server
 POSTGIS=2.1.1 r12113 GEOS=3.4.2-CAPI-1.8.2 r3921 PROJ=Rel. 4.8.0, 6
 March 2012 GDAL=GDAL 1.9.2, released 2012/10/08 LIBXML=2.7.6
 LIBJSON=UNKNOWN RASTER

 On Fri, Jun 5, 2015 at 10:55 AM, David Fawcett david.fawc...@gmail.com
 wrote:

 We are finally able to retire one of our older servers running an old
 version of PostGIS and we are migrating everything over to a new install.

 One thing that I am noticing is that I am getting different behavior
 between the two versions when transforming some values from 4267 to 4326.

 The Old Server:

 SELECT Postgis_Version();
 1.3 USE_GEOS=1 USE_PROJ=1 USE_STATS=1

 SELECT
 ST_Y(ST_Transform(ST_SetSRID(ST_Point(-94.884720995369,47.4732852509464),4267),4326))
 AS lat,

 ST_X(ST_Transform(ST_SetSRID(ST_Point(-94.884720995369,47.4732852509464),4267),4326))
 AS lon;

 47.4732375024506 | -94.8849980111078



 The New Server
 SELECT Postgis_Version();
 2.1 USE_GEOS=1 USE_PROJ=1 USE_STATS=1

 SELECT
 ST_Y(ST_Transform(ST_SetSRID(ST_Point(-94.884720995369,47.4732852509464),4267),4326))
 AS lat,

 ST_X(ST_Transform(ST_SetSRID(ST_Point(-94.884720995369,47.4732852509464),4267),4326))
 AS lon;

 47.4732852509464 | -94.884720995369

 This issue feels familiar, but I couldn't find anything about it when
 searching.  Can anyone shed some light?

 Thanks,

 David.



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




-- 
Richard W. Greenwood, PLS
www.greenwoodmap.com
___
postgis-users mailing list
postgis-users@lists.osgeo.org
http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users

Re: [postgis-users] Topology Attributes

[Richard Greenwood]

I don't know if this will help but here is a link to a PDF
http://www.greenwoodmap.com/presentations/WyGEO-Lander-2014.pdf of a
presentation I did a while ago. Starting on page 14 I am trying to
illustrate how attributes from a simple polygon source can be joined to
edges in PostGIS topology .

Rich

On Sun, Feb 8, 2015 at 2:29 AM, Ofer Zilberstein zilberstein.o...@gmail.com
 wrote:


 Hi,

 When you build topology from set of connected polygons, the original
 polygon attributes are not part of the topology model. The model creates
 MBR face for each original polygon.

 The edges hold the left  right faces.

 How or what is the right way to add to the edges the right and left
 original polygons ?

 --

 *Ofer Zilberstein*

 Mobile: 972-544886873

 Skype: ofer.zilberstein

 Mail: zilberstein.o...@gmail.com

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




-- 
Richard W. Greenwood, PLS
www.greenwoodmap.com
___
postgis-users mailing list
postgis-users@lists.osgeo.org
http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users

Re: [postgis-users] topology, joining attributes

[Richard Greenwood]

Sandro, Rémi,

Thanks for the suggestions. Lot's to explore!

Best regards,
Rich



On Thu, Jul 24, 2014 at 4:14 AM, Sandro Santilli s...@keybit.net wrote:

 On Thu, Jul 24, 2014 at 12:06:14PM +0200, Rémi Cura wrote:
  Hey,
  I think Sandro wrote a QGis plugin to edit topology, I never used it
 though.

 Yep, https://github.com/strk/qgis_pgis_topoedit

 Very simple, mostly only provides tools to clean them up, while qgis
 itself allows editing at the TopoGeometry level, leaving unused
 primitives that would then need to be cleaned up.

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




-- 
Richard W. Greenwood, PLS
www.greenwoodmap.com
___
postgis-users mailing list
postgis-users@lists.osgeo.org
http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users

Re: [postgis-users] polygons to linestrings

[Richard Greenwood]

Two really interesting and educational examples! Thank you both very much.
I learned a lot from both. Nicolas's is obviously a simpler. It produces a
few more linestrings than Rémi's because Rémi's connects more segments
together. But both accomplish what I want to do. Rémi's example is a very
elegant and complete example - thank you for taking the time. I like the
way you dynamically created test data and also snapping is a good idea that
I would have overlooked.

@Roxanne, thanks for your interest in my problem, sorry that I didn't
explain better. When you draw polygon layers with dashed or dotted line
styles they can look very bad because lines from adjacent polygons are
drawn over top of each other and the symbols (dots, dashes, etc.) do not
align. So I just want all the unique linestrings so that they will only be
drawn once. I want linestrings, not just individual segments, for
cartographic appearance and for performance. And since it's just for
cartography, I don't care about attributes, line direction, or topology.

Again, thank you very much. Your answers both solved my immediate problem
and taught me a lot.

Rich



On Fri, May 23, 2014 at 4:32 AM, Nicolas Ribot nicolas.ri...@gmail.comwrote:

 Hi Richard,

 You could use st_node on the polygon boundaries to rebuild unique,
 complete linestrings:
 Boundaries are dumped into linestrings in order to build a Multilinestring
 containing all PG boundaries.
 st_node is performed on this collection, then result is dumped into
 individual lines:

 with tmp as (
 select (st_dump(st_boundary(geom))).geom
 from mytable
 ) select nexval('seq01') as id, (st_dump(st_node(st_collect(geom.geom
 from tmp;

 If your dataset is big, a cut-by-grid approach could speed up the process.

 Nicolas


 On 23 May 2014 12:26, Rémi Cura remi.c...@gmail.com wrote:

 Hey,
 you have to keep the path of the dumped segments, and carefully choose
 which duplicates to keep, inorder to be able to reconstruct in the right
 order.

 (for the rest I'm referring to this function :
 https://github.com/Remi-C/_utilities/blob/master/postgis/rc_DumpSegments.sql
 )

 Supposing that your polygon have an unique ID, and that you only consider
 there boundaries (interior ring couldn't be shared as they can't touch the
 boundaries and you said you have no overlaps)

 _convert polygon to boundaries, generating a unique ID per
 polygon/boundaries
 _break each boundaries into segment and keep the path of each segment
 (for instance with my function)
 _now you can remove duplicate, but you have to order the querry so has to
 keep continuous parts
 _Then you can recompose your geometry :

 Here is an example :
 -

  WITH the_geom AS ( --creating a fake geom for test purpose, function
 available here :
 https://github.com/Remi-C/_utilities/blob/master/postgis/cdb_GenerateGrid.sql
 SELECT  row_number() over() AS id, geom
 FROM CDB_RectangleGrid(ST_GeomFromtext('polygon((0 0, 100 0, 100
 100, 0 100 , 0 0))'), 10,10) AS geom
  )
  ,dmp_seg AS ( --breaking the boundary of polygons into segments.
 Function is available here:
 https://github.com/Remi-C/_utilities/blob/master/postgis/rc_DumpSegments.sql
  SELECT id, rc_DumpSegments(ST_Boundary(geom)) as dmpgeom
  FROm the_geom
  )
 ,cleaned_ds AS ( --snapping to grid to avoid precision issue, replace 0.1
 by your alloxed precision
  SELECT id, (dmpgeom).path, ST_SNapToGrid((dmpgeom).geom,0.1) as geom
  FROM dmp_seg
  )
 ,dedup AS ( --deleting the duplicates in the segments, but not randomly :
 provide an order to be able ot reconstruct after
  SELECT DISTINCT ON ( geom  )  *
  FROM cleaned_ds
  ORDER BY  geom, id, path
  ) --reconstructing lines from segment, but again with the right order
  ,reconstructed_lines AS (
  SELECT id,   ST_MakeLine(array_agg(geom ORDER BY  dedup.path) ) as geom
  FROM dedup
 GROUP BY id
  ) --simple check, can be suppressed : it should output no row
  SELECT id, geom
  FROM reconstructed_lines
  WHERE st_IsValid(geom)  = FALSE
 -

 Cheers,

 Rémi-C



 2014-05-23 6:05 GMT+02:00 Roxanne Reid-Bennett r...@tara-lu.com:

  On 5/22/2014 12:28 PM, Richard Greenwood wrote:

  I have a polygon table that I want to convert to linestrings without
 any duplicated (overlapping) linestrings. I'm doing this simply to improve
 cartography so I'm looking for a quick and dirty approach, avoiding
 topology if possible.


 I'm not sure I understand what it is you are attempting to do from this
 description.  I would expect lines going in two directions from each
 polygon and I just don't know how you would use that to help with improve
 cartography.


  The closest I've gotten so far is to break the polygons boundaries into
 simple (two vertex) lines and delete all the duplicate lines. Now I'm stuck
 getting the simple lines back into polylines. Just doing a union of the
 whole mess isn't getting me individual linestrings.

 So, crazy out of the blue question.. have  you considered

[postgis-users] polygons to linestrings

[Richard Greenwood]

I have a polygon table that I want to convert to linestrings without any
duplicated (overlapping) linestrings. I'm doing this simply to improve
cartography so I'm looking for a quick and dirty approach, avoiding
topology if possible.

The closest I've gotten so far is to break the polygons boundaries into
simple (two vertex) lines and delete all the duplicate lines. Now I'm stuck
getting the simple lines back into polylines. Just doing a union of the
whole mess isn't getting me individual linestrings.

All this is turning into more steps that I anticipated and I wonder if I'm
missing simpler approach?

Thanks,
Rich

-- 
Richard W. Greenwood, PLS
www.greenwoodmap.com
___
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.

[Richard Greenwood]

On Mon, Apr 14, 2014 at 8:01 AM, Andy Colson a...@squeakycode.net wrote:

 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. :-)


There is also ALTER DEFAULT PRIVILEGES at the database level and at the
schema level. It is handy when you are adding stuff so that you don't have
to explicitly grant privileges every time.

ALTER DEFAULT PRIVILEGES IN SCHEMA some_schema
GRANT SELECT ON TABLES
TO unwashed;

-- 
Richard Greenwood
richard.greenw...@gmail.com
www.greenwoodmap.com
___
postgis-users mailing list
postgis-users@lists.osgeo.org
http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users

Re: [postgis-users] missing libxml2.la file when compiling PostGIS 2.1.1 from Source on Ubuntu 12.04

[Richard Greenwood]

Have you tried
   locate xml2-config
and then
  ./configure
--with-xml2config=/whatever/path/to/xml2-config/you/got/from/locate




On Mon, Nov 18, 2013 at 12:44 PM, Paolo Corti pco...@gmail.com wrote:

 On Mon, Nov 18, 2013 at 8:25 PM, Marcelo Soares Souza
 marc...@juntadados.org wrote:
  2.8.0+dfsg1-5ubuntu2.2~precise1
  2.8.0+dfsg1-5ubuntu2.2~precise1
 
Did you installed libxml2-dev package?
 

 As mentioned in my previous email:

 On Mon, Nov 18, 2013 at 8:22 PM, Paolo Corti pco...@gmail.com wrote:
  The libxml2 and libxml2-dev versions I am using is the one from ubuntu
  precise repository:

 also

  Note: I can successfully compile PostGIS 2.0.2 in the same machine

 The only stuff I found on the web regarding this missing libxml2.la
 file is here:
 http://forums.gentoo.org/viewtopic-t-925630-start-0.html

 Thanks anyway
 p

 --
 Paolo Corti
 Geospatial software developer
 web: http://www.paolocorti.net
 twitter: @capooti
 skype: capooti
 ___
 postgis-users mailing list
 postgis-users@lists.osgeo.org
 http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users




-- 
Richard Greenwood
richard.greenw...@gmail.com
www.greenwoodmap.com
___
postgis-users mailing list
postgis-users@lists.osgeo.org
http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users

Re: [postgis-users] radically different performance on different machines

[Richard Greenwood]

Sandro, Rémi,

Thanks for the suggestions. I've done quite a bit more work but still have
not found a solution. The query runs great on every computer except my
production server. My production server is an Amazon instance so I created
a new Amazon machine with same OS/Postgres/Postgis version. Backup,
restored my database into the new instance and the query runs great. I
compared postgresql.conf between the two machines and they are the same. So
I have something different on the two computers that is causing the query
planner to produce significantly different approaches to the same query on
the same database. But I am at a loss as to what.

Thanks for reading, any ideas appreciated.
Rich




On Fri, Nov 8, 2013 at 8:49 AM, Rémi Cura remi.c...@gmail.com wrote:

 Hey Richard,
 I may be completely wrong, but maybe you could use ST_DWithin instead of
 Buffer+intersect.
 It is usually a good boost in perf, but maybe you absolutely need this
 shrinking.

 In the same way I'm not sure the way this query is written is the best
 (you really need a subquerry here?).

 I'm guessing you don't have a simple index on your name from
 special_district, it will accelerate it.
 (CREATE INDEX ON special_district (name);)

 Last thing is you may consider to protect your column name owner and
 name as these are reserved sql word, using doublequote

 Hope it helps =)

 Cheers,
 Rémi-C


 2013/11/8 Sandro Santilli s...@keybit.net

 On Fri, Nov 08, 2013 at 07:58:45AM -0700, Richard Greenwood wrote:
  I have a query that runs in 1 second on one machine and 47 seconds on
  another. The postgres and postgis versions are bascially the same. The
  database and query are the same. But explain is different on the two.
 The
  query is below and the graphic output of pgAdmin explain is attached.
  Obviously, my question is - why the difference?

 Different statistics gathered ? Try running ANALYZE on both systems.
 Different cost configuration ? Compare postgresql.conf.

 --strk;
 ___
 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




-- 
Richard Greenwood
richard.greenw...@gmail.com
www.greenwoodmap.com
___
postgis-users mailing list
postgis-users@lists.osgeo.org
http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users

Re: [postgis-users] PostGIS 2.02 view problem

[Richard Greenwood]

In your map file you have using unique gid. Are you sure that there is a
gid column in your view?

Rich


On Wed, Jul 24, 2013 at 3:35 AM, karsten kars...@terragis.net wrote:

 **
 Hi all,

 I am trying to use a b view in Postgis 2.0.2 (POSTGIS=2.0.2 r10789
 GEOS=3.3.8-CAPI-1.7.8 PROJ=Rel. 4.8.0, 6 March 2012 GDAL=GDAL 1.10.0,
 released 2013/04/24 LIBXML=2.8.0 LIBJSON=UNKNOWN TOPOLOGY RASTER)
 for display via MapServer 6.2
 I am getting an this error from MapServer:

 Failed to draw layer named #39;members#39;.
 msPostGISLayerWhichShapes(): Query error. Error executing query: ERROR:
 column quot;COUNTquot; does not exist
 LINE 1: select
 quot;COUNTquot;,encode(ST_AsBinary(ST_Force_2D(quot;geomquot;),#39;NDR#39;).

 I have no idea why a count is needed here or where this field is coming
 from ...seems that the regular table have no issue with this.

 My VIEW public.geometry_columns  shows these values for the view:
 xy;xy;myloc;geom;2;4269;POINT

 What can I do to fix this in Postgis ?
 See below for view details

 Cheers
 Karsten

 Terra GIS LTD
 -
 -- View:
 CREATE OR REPLACE VIEW xy.test
 AS
  SELECT xy.census_city_id, st_centroid(places.geom)::geometry(Point,4269)
 AS geom, places.name
FROM
 myschema.xy,myschema.places

   WHERE xy.census_city_id =
 places.geoid::integer;

 ALTER TABLE
 myschema.xy_loc

 OWNER TO myuser;

 ---
 in mapfile:

 DATA geom from (select * from myschema.xy) as temp using unique gid using
 SRID=4269

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




-- 
Richard Greenwood
richard.greenw...@gmail.com
www.greenwoodmap.com
___
postgis-users mailing list
postgis-users@lists.osgeo.org
http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users

Re: [postgis-users] Link beween the_geom of 2 tables

[Richard Greenwood]

You could do that with triggers but I would not recommend it. In a
normalized database things should not be duplicated and that goes for
geometries. I would recommend that you put your geometries in a separate
table. Table A and Table B would join to the geometries table.

Rich


On Thu, Jul 18, 2013 at 9:15 AM, Melin Maxence maxenceme...@yahoo.frwrote:

 Hi,

 Do you know if it is possible to have in the_geom field of one record in a
 table A, a link (query, hypertext, something else...) to the_geom field of
 another record in a different table (table B), so that when the geom is
 modified in the record of the table A, it is automatically modified in the
 related record of the table B ?

 Thanks for your answers !

 Max

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




-- 
Richard Greenwood
richard.greenw...@gmail.com
www.greenwoodmap.com
___
postgis-users mailing list
postgis-users@lists.osgeo.org
http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users

Re: [postgis-users] Import CSV (was: Noob question with shp2pgsql)

[Richard Greenwood]

On Sat, Apr 13, 2013 at 6:54 AM, Margie Roswell mrosw...@gmail.com wrote:


 But the shp2pgsql thread yesterday got me thinking: to import a shapefile,
 they've created a utility so that *we don't have to set up the structure
 of the table in advance*

 Is there something similar on the CSV side?


ogr2ogr does a wonderful job of that.

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

Re: [postgis-users] unique id in QGIS/PostGIS

[Richard Greenwood]

On Sat, Apr 6, 2013 at 2:18 AM, Margie Roswell mrosw...@gmail.com wrote:

 I've been googling up a storm, now that I've got a local postgis database
 with QGIS. But still haven't figured out how to display a newly created
 table.

 The query I used to create the table:
 CREATE TABLE smaller_leg_final AS
 SELECT ST_Buffer(geom,-500) AS geom, legislative_final.district
 FROM legislative_final
 WHERE area  30;

 I then discovered that I needed a unique ID, and did the following:
 ALTER TABLE smaller_leg_final ADD COLUMN id serial not null;



Why don't you try making you unique column a primary key?

ALTER TABLE smaller_leg_final
  ADD CONSTRAINT smaller_leg_final_pk PRIMARY KEY(id);

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

Re: [postgis-users] Google Polyline into PostGIS

[Richard Greenwood]

On Fri, Feb 15, 2013 at 6:44 AM, James David Smith 
james.david.sm...@gmail.com wrote:

 Dear all,

 I've had a quick search online and found a few people who have had
 similar questions, but no definite answers. My question is whether
 there is a simple way to take an encoded Google Polyline and place it
 into a Geometry column of type Polyline within PostGIS (that PostGIS
 understands and can work with).

 By way of background, I am using the Google Directions API to generate
 some routes, and I want to store the results in a PostGIS database.


I do not know what a encoded Google Polyline is but I believe Google can
output KML. ogr2ogr can read KML and write to postgis, so that's where I
would start.

Rich

-- 
Richard Greenwood
richard.greenw...@gmail.com
www.greenwoodmap.com
___
postgis-users mailing list
postgis-users@lists.osgeo.org
http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users