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>
Andy Becker | 8 Oct 02:41 2014

ST_MakePoint vs. ST_MakePointM

Hi all - Sorry if this is a long-resolved design decision, but is there a good 
reason that MakePoint accepts double precision inputs while MakePointM only 
accepts single-precision floats?

In my use cases single-precision resolution is not fine enough, such that 
MakePointM can't be used.  My tables will accordingly need a double-precision 
POINT geometry object and then a separate column for the m-value.

Mark Wynter | 4 Oct 14:18 2014

Re: postgis-users Digest, Vol 152, Issue 3

Don't have the benefit of seeing your image...
For those that intersect, what about ST_difference to rid overlaps and ST_Snap to fill voids... 

Or ST_Buffer both, take the intersection, union it to one of the polys and difference it with the first

I recall a project 6 months back where client handed me a bunch of polys that lacked common edges because they
were created almost freehand ... 

I can't pull up my code because I'm on annual leave, but I recall I wrote a stored procedure that looped
through each poly at a time, found the nearest, and used a combination of functions to produce a valid edge
between each pair.


Sent from my iPhone

> On 4 Oct 2014, at 4:30 am, postgis-users-request <at> wrote:
> Send postgis-users mailing list submissions to
>    postgis-users <at>
> To subscribe or unsubscribe via the World Wide Web, visit
> or, via email, send a message with subject or body 'help' to
>    postgis-users-request <at>
> You can reach the person managing the list at
>    postgis-users-owner <at>
> When replying, please edit your Subject line so it is more specific
> than "Re: Contents of postgis-users digest..."
> Today's Topics:
>   1. A little tired of non-noded intersections between    polygons --
>      node them all (John Abraham)
> ----------------------------------------------------------------------
> Message: 1
> Date: Fri, 3 Oct 2014 12:29:30 -0600
> From: John Abraham <jea <at>>
> To: postgis-users <at>
> Subject: [postgis-users] A little tired of non-noded intersections
>    between    polygons -- node them all
> Message-ID: <5471EFE5-B80B-45E5-8154-B8C3F691BBDD <at>>
> Content-Type: text/plain; charset="windows-1252"
> I?m trying to clean up a geometry layer, a division of a province into smaller areas.  Once it?s cleaned up
I?d like to start using a topology, so that no-one else ever has to deal with these problems? but? meanwhile...
> I keep getting non-noded intersections or other topology exceptions when I?m searching for (or
removing) overlapping polygons or searching for (or adding in) missing bits between polygons.
> I?ve used all kinds of combinations of st_buffer(0), st_cleangeometry(), custom versions of
st_cleangeometry(), and st_snaptogrid to try to fix things.
> I?ve noticed a pattern with the types of problems I?m currently dealing with.  Basically, they are
non-noded intersections between polygon edges.  Eg, there is a polygon that has a
not-quite-exactly-north-south boundary on its west side that it shares with its western neighbor.  But
the neighbor's boundary is longer, extending north past the corner of the original, and it?s defined by a
different pair of points.  If the image comes through, you can see it below.  The Greenish polygon obviously
has node at its north-west corner, but the brownish polygon, to the west of it, does not have a node at that
location, its boundary is longer so is defined by more distant end points.
> I?m wondering if I could use something like st_node to add a node in the western polygon, so the corner
between them is defined identically on both sides? Then, presumably, I wouldn?t be getting these 
> ERROR: GEOSUnaryUnion: TopologyException: found non-noded intersection between LINESTRING (144297
5.66546e+006, 144201 5.67011e+006) and LINESTRING (144203 5.67002e+006, 144204 5.67002e+006) at
144203.19006961776 5670017.7300232062
> type of errors.  But ST_Node only works on lines, not polygons.
> PS I was watching Paul Ramsey?s presentation on the web from Foss4G and I agree that PostGIS is fantastic
and gaining so much momentum, and perhaps even ?asymptotically approaching perfection?.  But these
types of topology errors always seem to trip me up.  Isn?t there something that can be done in the underlying
GEOS functions to make these less common?  Or is everything an edge case that needs a special algorithm?  I?m
frankly rather surprised that these errors don?t seem common for more people (based on my limited google
search hits): are others not using ST_Union or ST_Intersects very much?  Or does everyone else have
boundaries that are defined in topologies, or with every point specified?  Or is there just some trick,
that no-one?s ever told me about, to add in the necessary points
  on edges so that identical line segments are defined by the same pairs of points?
> Feeling frustrated,
> --
> John Abraham
> jea <at>
> 403-232-1060
> -------------- next part --------------
> An HTML attachment was scrubbed...
> URL: <>
> -------------- next part --------------
> A non-text attachment was scrubbed...
> Name: Screen Shot 2014-10-03 at 12.08.00 PM.png
> Type: image/png
> Size: 7887 bytes
> Desc: not available
> URL: <>
> ------------------------------
> _______________________________________________
> postgis-users mailing list
> postgis-users <at>
> End of postgis-users Digest, Vol 152, Issue 3
> *********************************************
John Abraham | 4 Oct 00:02 2014

Re: A little tired of non-noded intersections between polygons -- node them all

Wow Martijn.

Thank you SO much.  It seems to have worked.  This will get a lot of use, I think.  You really should get it into PostGIS as a function, for better visibility and for the benefit of the world.  

I can’t wait to try it on one of my larger problems (e.g. a cadastral data set with about 2 million parcels).

For everyone else, here’s the paper:

John Abraham

On Oct 3, 2014, at 1:23 PM, Martijn Meijers <b.m.meijers <at>> wrote:

Hi John,

We know the pain you describe.

For this we've developed at our research group:


Documentation here:


Not integrated with PostGIS at the moment and only reading shapefiles, but it should clean exactly the type of data that you describe into properly noded polygons (with gaps and overlaps removed/filled).


Martijn Meijers

mailto:b.m.meijers <at>

Faculty of Architecture and the Built Environment
Delft University of Technology

P.O. Box 5030 | Julianalaan 134 (building 8), room 01.oost.330
2600 GA Delft | 2628 BL Delft
The Netherlands

tel (+31) 15 2785642

postgis-users mailing list
postgis-users <at>