gmane.comp.gis.postgis

Re: toTopoGeom performance tips

Luca Morandini — 2012-05-17T01:14:55

Once more on the bleeding edge... I have talked to the project manager, and we are 
willing to help; just allow me three or four working days to set up a test bed 
according to your requirements.



Thanks for checking.

Regards,

Luca Morandini
Data Architect - AURIN project
Department of Computing and Information Systems
University of Melbourne

Re: use index for "order by xmin(geom)"

Mike Toews — 2012-05-16T23:24:45

One way to do this is to make an index using that function:

CREATE INDEX mytable_xmin_idx ON mytable (ST_Xmin(geom));

Then check the planner to make sure the index is being used:

EXPLAIN ANALYSE SELECT *
FROM mytable
ORDER BY ST_Xmin(geom);

See if that speeds things up.
-Mike

Re: use index for "order by xmin(geom)"

Melchior Moos — 2012-05-16T21:10:01

Sorry, actually st_xmin is the function I'm after. I want to select
the whole table ordered by minimum x coordinate of the gemertries.
When I do it the naive way postgresql needs half an hour to prepare
the ordering before the first results are delivered, since my table is
quite large. I thought that it could be somehow possible to skip this
time since the ordering is already stored in the index on the geometry
column...

On 17 May 2012 03:05, Melchior Moos <melchior.moos at gmail.com> wrote:

Linear referencing with PostGIS

Melpati, Muni — 2012-05-16T20:59:11

We have various web-based GIS applications that consume the data from enterprise database (oracle/ArcSDE) . These applications need linear referencing capabilities of the ArcSDE. We currently do this using java API provided by ESRI. Some of these web application provide user capabilities to query distance, in miles, of a road segment give a location on the road. Our data has linear referencing capabilities and other features such as topology which are essential to our business needs. I came to know while older PostGIS doesn't provide such capabilities. I am wondering if newer versions offer any similar functionalities. Is it possible to provide similar capabilities with PostGIS and Postgresql? Thanks in advance.
_______________________________________________
postgis-users mailing list
postgis-users< at >postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users

Re: use index for "order by xmin(geom)"

Mike Toews — 2012-05-16T19:23:11

Hi Melchior,

"xmin" is actually a PostgreSQL system column used for transactions,
not a PostGIS function.

http://www.postgresql.org/docs/current/static/ddl-system-columns.html

The KNN nearest neighbour feature that I think you are describing is
the <-> and <#> operators. Check out the documentation for more:

http://postgis.refractions.net/docs/geometry_distance_centroid.html
http://postgis.refractions.net/docs/geometry_distance_box.html

-Mike

On 17 May 2012 03:05, Melchior Moos <melchior.moos< at >gmail.com> wrote:

Re: Closing polylines

2012-05-16T18:24:34

Hi George,

There is a simple example of a script using topology on the wiki at:
http://trac.osgeo.org/postgis/wiki/UsersWikiTopologyExample
& more help at
http://trac.osgeo.org/postgis/wiki/UsersWikiPostgisTopology


The attached script was written to take the New Zealand mainland coastline comprised of various linestrings which had small gaps between them in some cases, & build a polygon from them. To do this it iterated through the linestrings, loading them into the topology one at a time with a small snapping distance to ensure all the start/end nodes aligned correctly. 

It also loads data from various shapefiles of island polygons into the table & exports a shapefile of the entire national coastline as polygons. I think you can pull out the appropriate SQL commands from this to do what you are asking about.

I haven't tried this with multilinestrings, only single linestrings. 

If you need additional help, feel free to ask, though I'm just learning the topology stuff myself.

Cheers,

   Brent Wood

Re: Closing polylines

Nicolas Ribot — 2012-05-16T18:08:08

Hi George,

If the segments are touching each other, like in your sample data, indeed
topology could help a lot to rebuild closed lines.

Here, on this sample, st_polygonize is giving great results. It connects
segments of the same contour and fill it to generate a polygon.
Its exteriorRing gives the actual contour:

select elev, st_exteriorRing(st_geometryN(st_polygonize(geom), 1))
from seg
group by elev;

Do you have some gaps for some contour lines, or they are all connected ?

Nicolas

On 16 May 2012 16:35, Nicolas Ribot <nicolas.ribot< at >gmail.com> wrote:
_______________________________________________
postgis-users mailing list
postgis-users< at >postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users

use index for "order by xmin(geom)"

Melchior Moos — 2012-05-16T15:05:05

I recently read that postgis 2.0 can use the index in order by clauses
to find nearest neighbours of geometries. Is there also a way to use
the index for queries like
SELECT * FROM xy ORDER BY xmin(geom); ?
Best regards,
Melchior Moos

Re: Closing polylines

Nicolas Ribot — 2012-05-16T14:35:29

Hi,

I will have a look at it.

Nicolas

On 15 May 2012 11:17, george wash <gws293< at >hotmail.com> wrote:

Re: PostGIS KNN best practices

Stephen V. Mather — 2012-05-16T12:56:40

Ah, I had hopes pinned on the idea that I just wasn't smart enough to figure
it out, but it's an inherent limitation.  I will be using your function
though-that is a clean way to encapsulate the functionality.

 

 

http://www.clemetparks.com/images/esig/cmp-ms-90x122.pngStephen Mather
Geographic Information Systems (GIS) Manager
(216) 635-3243

svm< at >clevelandmetroparks.com
 <http://www.clemetparks.com/> clevelandmetroparks.com

 

 

 

 

From: postgis-users-bounces< at >postgis.refractions.net
[mailto:postgis-users-bounces< at >postgis.refractions.net] On Behalf Of
Alexandre Neto
Sent: Wednesday, May 16, 2012 7:35 AM
To: PostGIS Users Discussion
Subject: Re: [postgis-users] PostGIS KNN best practices

 

I have been around that question to.

 

http://gis.stackexchange.com/questions/24456/nearest-neighbor-problem-in-pos
tgis-2-0-using-gist-index-function 

 

You have to do it in two steps, like is explained in the operator page
<http://postgis.refractions.net/docs/geometry_distance_centroid.html> . One
faster step

Re: segmentation fault

Gold, Jack L (US SSA — 2012-05-16T12:48:07

It seems that going through each of the individual libraries (proj.4, geos, gdal, and postgis-2.0) and running make clean, make install yielded new libraries that worked without the segfault.  I'm thinking that old 32-bit object files were lying around in the previous build environment and something managed to link incorrectly.

--Jack Gold

-----Original Message-----
From: postgis-users-bounces< at >postgis.refractions.net [mailto:postgis-users-bounces< at >postgis.refractions.net] On Behalf Of Gold, Jack L (US SSA)
Sent: Tuesday, May 15, 2012 8:43 AM
To: PostGIS Users Discussion
Subject: Re: [postgis-users] segmentation fault

I decided to run a core dump and see if I can spot why the call to the library is causing the crash.  I still don't know what is causing the crash, but I suspect it's one of the features I compiled into the postgis library.  Below is the dump and a file listing that indicates that all the libraries are indeed x86_64:

[root< at >localhost data]# gdb /usr/pgsql-9.1/bin/postgres core.3252 GNU gdb (GD

Re: PostGIS KNN best practices

Alexandre Neto — 2012-05-16T11:35:06

I have been around that question to.

http://gis.stackexchange.com/questions/24456/nearest-neighbor-problem-in-postgis-2-0-using-gist-index-function


You have to do it in two steps, like is explained in the operator
page<http://postgis.refractions.net/docs/geometry_distance_centroid.html>.
One faster step to reduce the candidates (by using <-> or <#>) and second
one to get the real distances with ST_Distance.

The problem in finding the KNN for each row in a table is the fact that the
gist index <-> operator only works if one of the geometries is constant.
The workaround would be to create a SQL function to apply to each of the
rows using table.the_geom as a parameter.

Something like this:

----
CREATE OR REPLACE FUNCTION _enn2 (geometry) RETURNS double precision AS $$

WITH index_query as
(SELECT ST_Distance($1,f.the_geom) as dist
FROM "grelha5m" As f
ORDER BY $1 <#> g1.the_geom limit 1000)
SELECT dist
FROM index_query
ORDER BY dist;

$$ LANGUAGE SQL;
---

and I call it like this:

---
Select c.gid as gid

Re: Correct or wrong raster image loading

Giannis Giakoumidakis — 2012-05-16T10:17:16

As I found it out myself, I'll answer in case someone wants to work with that in the future!

The Postgis connection plugin in QGIS cannot add raster data, only shapefiles. In order to load, view and edit rasters in QGIS you have to add wtkraster plugin: http://plugins.qgis.org/plugins/wktraster/version/0.5.3/ and then works fine and easy.





________________________________
 From: Giannis Giakoumidakis <ggiakoumidakis< at >yahoo.com>
To: PostGIS Users Discussion <postgis-users< at >postgis.refractions.net> 
Sent: Tuesday, May 15, 2012 11:05 AM
Subject: Re: [postgis-users] Correct or wrong raster image loading
 

All right, I got that, thank you. 


So is there anyone who uses QGIS plugin to Postgis and managed to display raster data, because I can't and I don't know what's the problem? Which part of the database I have to Add in order to display my rasters in QGIS platform?






________________________________
 From: Bborie Park <bkpark< at >ucdavis.edu>
To: postgis-users< at >postgis.refractions.net 
Sent: Monday, May 14,

Re: toTopoGeom performance tips

Sandro Santilli — 2012-05-16T08:19:29

Ok, then it's not an issue with transactions.

If you want to help further profiling please:
 (1) install latest GEOS from 3.3 branch
 (2) install latest postgis from trunk (make sure to upgrade topology scripts)
 (3) see how time relates to topology primitives population density, see if a
     specific geometry is taking a visible lot more than others to import,
     enable debugging in topology to figure where the time goes.

PS: your queries do look fine.

--strk;

  ,------o-. 
  |   __/  |    Delivering high quality PostGIS 2.0 !
  |  / 2.0 |    http://strk.keybit.net - http://vizzuality.com
  `-o------'

Re: z,m,zm geometries

Paul Ramsey — 2012-05-16T05:59:28


Oh, you're getting text and PostgreSQL has some quite powerful regex
functions. You can probably strip the Z from the text before passing
it along to PostGIS.

P.

Re: z,m,zm geometries

Paul Ramsey — 2012-05-16T05:58:48

Are you wedded to the Z? Do you have a way to strip the Z from the
ESRI geometry before outputting the text?
Perhaps you could try WKB instead? (Still might have problems with
type numbers, but maybe worth a try?)


The 1.0 and 1.1 specs made no mention of higher dimensions (and hence
provided no guidance on what form the WKT should take). Not sure if
1.2 did, but I don't think so. ISO SQL/MM does talk about them, and
that's the pattern ESRI is following. PostGIS 2.0 consumes that kind
of text, but that's no solace to you.

P.

Re: One-click installer or building from the sourcecode (postgresql)

Ben Madin — 2012-05-16T01:18:51

G'day Muni,

A good rule for getting an answer on a mailing list is to ask a clear question - if you don't get a response after three weeks you might want to consider rephrasing it, or being more specific.

You have provided very little information on what you are actually trying to achieve, or on what sort of system, what constraints you are working under or what scale and type of data you are managing.

For a start, typing "postgresql arcsde" into google returned at least 60 000 records, the first ten of which included an ESRI supplied installation guide and a number of blogs and email lists discussing this combination, I would suggest you try this route, and then bring in some specific questions.

good luck

Ben




On 15/05/2012, at 2:14 AM, Melpati, Muni wrote:


_______________________________________________
postgis-users mailing list
postgis-users< at >postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users

Re: Closing polylines

2012-05-15T18:16:15

You can work around this problem by using the Postgis topology capability. It will build polygons (faces) from such lines which you can use as topopolygons or extract as conventional geometries.

--- On Tue, 5/15/12, george wash <gws293< at >hotmail.com> wrote:

From: george wash <gws293< at >hotmail.com>
Subject: Re: [postgis-users] Closing polylines
To: postgis-users< at >postgis.refractions.net
Date: Tuesday, May 15, 2012, 7:30 PM


  

    
  
  
    Thank you Nicolas, I understand the second point (a
      single segment poly) but I am having a different sort of problem
      from your first point. I haven't been able to pinpoint it yet but
      I suspect it happens when the segments are touching, e.g. the
      endpoint of one is same as the startpoint of another one (but the
      segments are not necessarily in an orderly sequence). I also
      suspect that the problem is caused by the start and end points
      being at opposite ends of the segments eg 1....10,
      20.....10,10.....1 so that the closest segmen

PostGIS KNN best practices

Stephen V. Mather — 2012-05-15T16:37:18

Hi All,

                Pretty excited by the new operators <-> and <#>, but a bit
confused as to how to use them in a query.  The two examples from P. Ramsey
back in late 2011 (
http://blog.opengeo.org/2011/09/28/indexed-nearest-neighbour-search-in-postg
is/ ) included doing a KNN on a single point to a cloud of points, i.e. 

 

SELECT name, gid

FROM geonames

ORDER BY geom <-> st_setsrid(st_makepoint(-90,40),4326)

LIMIT 10;

 

or doing KNN on non-point different geometries, where the first neighbor by
<-> or <#> might not be truly the first i.e.

 

with index_query as (
  select
    st_distance(geom, 'SRID=3005;POINT(1011102 450541)') as distance,
    parcel_id, address
  from parcels
  order by geom <#> 'SRID=3005;POINT(1011102 450541)' limit 100
)
select * from index_query order by distance limit 10;

 

So, how would one grab the first nearest neighbor for all points in a
dataset?  This is how I used to do it:

 

CREATE TABLE n2180_560_height AS 

SELECT x, y, height FROM 

(SELECT DISTINCT ON(ve

z,m,zm geometries

Duarte Carreira — 2012-05-15T13:02:47

Hi there.

I'm trying to convert from esri's st_ geometry type to postgis' one.

I can't convert to wkt using esri's functions and build a geometry in
postgis using geomfromtext.

Problems start when there are z, m, or zm coordinates. Mind that I'm on
postgis 1.4 since that's the latest supported by esri.

Now, when I convert from esri using their function I get a text like:

LINESTRING Z ...

When I pass it to geomfromtext, or even st_geomfromewkt, postgis does not
like this...

I posted on esri's forum and got a slap in the hand saying postgis is not
following the ogc standard...

I've done some reading and all I could find are references to POINTZ,
POLYGONZ, etc. (suffixes withouth spaces) in v.1.2.0 of the standard. But
this does not seem to be the spec for encoding wkt, just the geometry type
to be put into geometry_columns. Either way, I could not get postgis 1.4 to
accept pointz or any other geometryz type. I found references to postgis
2.0 doing this... but that is beyong my grasp for now.

So my 2 q

Re: segmentation fault

Gold, Jack L (US SSA — 2012-05-15T12:43:19

I decided to run a core dump and see if I can spot why the call to the library is causing the crash.  I still don't know what is causing the crash, but I suspect it's one of the features I compiled into the postgis library.  Below is the dump and a file listing that indicates that all the libraries are indeed x86_64:

[root< at >localhost data]# gdb /usr/pgsql-9.1/bin/postgres core.3252
GNU gdb (GDB) CentOS (7.0.1-42.el5.centos)
Copyright (C) 2009 Free Software Foundation, Inc.
License GPLv3+: GNU GPL version 3 or later <http://gnu.org/licenses/gpl.html>
This is free software: you are free to change and redistribute it.
There is NO WARRANTY, to the extent permitted by law.  Type "show copying"
and "show warranty" for details.
This GDB was configured as "x86_64-redhat-linux-gnu".
For bug reporting instructions, please see:
<http://www.gnu.org/software/gdb/bugs/>...
Reading symbols from /usr/pgsql-9.1/bin/postgres...(no debugging symbols found)...done.

warning: core file may not match specified executable file.
[N