Jochen Albrecht | 24 Oct 06:32 2014

Replicating ArcGIS relationship classes in PostGIS

I am working on behalf of a small non-profit that has so far used ArcGIS but is interested in moving open source. What kept them from making the move so far is that they are heavy users of relationship classes. The only discussion (inconclusive) that I could find was initiated by Lee Hachadoorian in 2008. Lots has changed since and I am wondering whether anybody has actually done this (implementing relationship classes in PostGIS). For example, back then, Lee suggested a trial-and-error approach to understand the encoding of relationship class tables. Have we gotten any further on this front? I am surprised that GIS Stack Exchange has no mentioning of this at all (assuming the search tools work).
We are using relationship classes for 1:many and many:many relationships. This is not a problem for PostGIS; I just don't know how to migrate the geodatabase keeping those relationships intact.
postgis-users mailing list
postgis-users <at>
Rémi Cura | 23 Oct 15:33 2014

Oriented BBox Efficient computing?

Hey list,
somebody has a good idea about how efficiently computing an approximate oriented bbox for a polygon ?

The simplest approaches I can think of are
 - iteratively rotate the geom by few degrees, take the envelop. Keep ration where envelope is min
 - using Principal Components Analysis  : extract points regularly spaced on border of object,  compute PCA .

postgis-users mailing list
postgis-users <at>
Bennos | 23 Oct 11:14 2014

pgsql2shp quiet mode


Is there any way to start a pgsql2shp command in quiet mode? There is
nothing in the documentation and using the -q option does not work.

If it is not contained, is there any chance it will be implemented in a
future version?


View this message in context:
Sent from the PostGIS - User mailing list archive at
Alexander.Mahrou | 17 Oct 22:23 2014

ST_Split just doesn't work for me.

Hello List,


I’ve got two polyline tables. I’m attempting to segment my lines by the intersections of the other lines.


create table segments as SELECT ST_CollectionExtract(ST_Split(a.newgeom,b.geom),2) as geom from

                line1 a, line2 b WHERE ST_Intersects(a.newgeom,b.geom) = true


The end result is the geometry that I started with. What am I doing wrong?


Alex Mahrou

Oil, Gas & Chemicals



9191 S. Jamaica St.

Englewood, CO 80112


postgis-users mailing list
postgis-users <at>
Jake O'brien Fagan | 16 Oct 21:02 2014

cannot drop function addgeometrycolumn

Howdy Gang,

Sorry if this question has been answer, but I've been searching all morning and nothing has produced results so far. When running a Rails migration, I received the following error:

 ** [out ::] ActiveRecord::
StatementInvalid: PG::AmbiguousFunction: ERROR:  function addgeometrycolumn(unknown, unknown, integer, unknown, integer) is not unique
 ** [out ::] LINE 1: SELECT AddGeometryColumn('boundaries', 'boundary', 4326, 'PO...
 ** [out ::] ^
 ** [out ::] HINT:  Could not choose a best candidate function. You might need to add explicit type casts.

After some time on the Postgis IRC, a nice fellow told me to run `psql -d fart_staging -f postgis_upgrade_21_minor.sql`. However, that returned:

psql:postgis_upgrade_21_minor.sql:53: ERROR:  cannot drop function addgeometrycolumn(character varying,character varying,character varying,character varying,integer,character varying,integer,boolean) because extension postgis requires it
HINT:  You can drop extension postgis instead.

Here is some relative info on my installation:

househappy_staging=# SELECT PostGIS_full_version();
 POSTGIS="2.1.3 r12547" GEOS="3.3.3-CAPI-1.7.4" PROJ="Rel. 4.7.1, 23 September 2009" GDAL="GDAL 1.9.0, released 2011/12/29" LIBXML="2.8.0" LIBJSON="UNKNOWN" TOPOLOGY RASTER
(1 row)

househappy_staging=# select version();
 PostgreSQL 9.3.5 on x86_64-unknown-linux-gnu, compiled by gcc (Debian 4.7.2-5) 4.7.2, 64-bit
(1 row)

Can anyone help me understand and debug this issue? I have for sure updated my postgis and postgresql a few time over the last two years starting with 9.0.

Jake O'Brien Fagan

Ruby Engineer
Facebook | Twitter | Instagram

postgis-users mailing list
postgis-users <at>
Pietro Rossin | 16 Oct 14:16 2014

unable to modyfy records after trigger execution

Hello everybody
I'm absolutely not a postgresql/postgis guru
I need to populate a postgresql/postgis table with microsoft access.
In Qgis I add the geometries and then I use MS Access to add most of the
tabular data.

To map the user who makes the last modification to the values I made a
trigger function like this:

CREATE OR REPLACE FUNCTION myschema.myfunction()
  RETURNS trigger AS
	new.user_modif =current_user;
	new.data_modif = now();
  COST 100;
ALTER FUNCTION myschema.myfunction()
  OWNER TO myself;

It is invoched by this trigger:

CREATE  TRIGGER set_user_date_mod
  ON myschema.mytable
  EXECUTE PROCEDURE myschema.myfunction();

This trigger works and if I modify any value in the table user and timestamp
are correctly  written at theyr place..

Then I encounter problems in other modification I want to perform on some
previously modificated rows..
Access can't save modifications and tells me there is another user (myself)
who has done some modification on the records that could be lost, and I
can't save..

Otherwise I can modify any other record.. And when modifyed that record is
not updatable anymore and so on..

Where is the problem?
I think there shoul be some transaction that dont come to the end and the
record remains "appended"..

Is it possible??

Any solution?

View this message in context:
Sent from the PostGIS - User mailing list archive at
Stephen Crawford | 13 Oct 18:45 2014


Hello All,

I want to copy a postgis table from one database to another.  Is dumping 
to a shapefile the best way to do this? Any other method? Pros and cons?



Stephen Crawford
Center for Environmental Informatics
The Pennsylvania State University
Pietro Rossin | 14 Oct 13:39 2014

function trigger to populate values based on spatial relationships

Hi all
I'm trying to write a trigger function to automatically populate data for
some fields based on spatial relationships
I want to control if the new point falls inside a set of polygons
(monitoraggio_acque.corpi_idrici_cw_tw) and if positive I want to take a
value to be written in "cod_ci_sup" from there..

If the added point falls externally to the previous polygons I want to take
values from other postgis tables, based on distance and overlap relationship

This is the trigger:
CREATE TRIGGER settavaloriautomaticiscarichi
  ON catasto_scarichi.aa_impianti_pn

And this is the relative funcion:

  RETURNS trigger AS
	IF (SELECT NEW.geom&&b.geom from (select geom from
monitoraggio_acque.corpi_idrici_cw_tw) b where st_contains(b.geom,
NEW.geom))= 't' THEN
	NEW.cod_ci_sup = (SELECT cod_corpo FROM
monitoraggio_acque.corpi_idrici_cw_tw b WHERE NEW.geom&&b.geom and
st_contains(b.geom, NEW.geom));
	NEW.cod_asta_reg = (SELECT codice_fvg FROM
idrologia.idrfvg_reteidrografica_l a WHERE st_dwithin (NEW.geom, a.geom,
1000) ORDER BY st_distance(NEW.geom, a.geom) asc limit 1);
	end if;
  COST 100;

If I try to add values from QGis 2.4 I don't get any message but no value is
Where is my mistake??


View this message in context:
Sent from the PostGIS - User mailing list archive at
Christopher Mutel | 13 Oct 18:29 2014

Union of topologies

Dear all-

Is there a way to create a new topology geometry as the union of two
existing topology geometries without casting to a normal geometry and
back? I have tried to write queries merging two TopoElementArrays, and
then use CreateTopoGeom, but so far without success.

Any help would be greatly appreciated.



Chris Mutel
Technology Assessment Group, LEA
Paul Scherrer Institut
5232 Villigen PSI
Telefon: +41 56 310 5787
Zenon Panoussis | 10 Oct 23:45 2014

Line intersects

Hello everyone

I have a number of OSRM routes and I'm looking for a way to find
intersections between them and order them by the length of the line
between intersections. Say for example that I have the following:

A: Brussels - Aachen - Köln - Montabaur - Frankfurt a.M. - Fulda.
B: Eindhoven - Venlo - Koblenz - Mainz - Aschaffenburg.
C: Venlo - Köln - Montabaur - Wiesbaden.
D: Tilburg - Eindhoven - Venlo - Koblenz - Mainz - Aschaffenburg - Würzburg.
E: Maastricht - Venlo -Duisburg - (ehum, Bielefeld) - Hannover.

A to D intersect each-other at more than one point, so each one
of them can substitute the others for part of the way. E does not
intersect A and intersects B, C and D at one single point, so it
cannot substitute any of them. B and D overlap exactly part of the
way, so D can substitute B completely while B can only substitute
D partially.

What I need to do is to test all of them against all other and come
up with those that can replace the shortest or the longest stretch
of the route that they are being matched against.

The raw data is a GPX of edge points like
<rtept lat="38.895080" lon="22.435042"></rtept>
<rtept lat="38.894685" lon="22.434856"></rtept>

Can anyone suggest an efficient way to do this kind of matching?

postgis-users mailing list
postgis-users <at>
Ben Madin | 9 Oct 09:30 2014

postgis_full_version error

G'day all,

I'm not sure if this is a postgis or an ubuntu issue, but having just installed postgis on to ubuntu 14.04 from the pgdg repository, I am getting this error :

  # select postgis_full_version();
  ERROR:  function postgis_jts_version() does not exist
  LINE 1: SELECT postgis_jts_version()
  HINT:  No function matches the given name and argument types. You might need to add explicit type casts.
  QUERY:  SELECT postgis_jts_version()
  CONTEXT:  PL/pgSQL function postgis_full_version() line 15 at SQL statement

I have previously installed onto many Ubuntu 12.04 without this problem. I guess there are two parts:

1. do I have / need jts (it is showing geos 3.4.2-CAPI-1.8.2 r3921) ?

2. if I don't have it, should it fail completely, or just return NULL ?




Ben Madin

postgis-users mailing list
postgis-users <at>