Tahir Tamba | 29 Jan 21:22 2015
Picon

Creating a DBlink to load data from Oracle Spatial to PostGIS

Hi,

I have a mapping project for generating an atlas from multiple layers from QGIS project. All my layers are stored in PostGIS. For cons, I have many layers to add that are stored in Oracle Spatial database and must be part of the project. This tables are updated in live every day. This is the reason why I don't have any choice to access  the data from Oracle database. I already have privileges to access data from Oracle Spatial schema that contains spatial tables.

 My question is the following . Is it possible to make a DBLink to load the Oracle Spatial database  tables in PostGIS database? If yes  what is the procedure and steps for loading Oracle Spatialtables into PostgreSQL / PostGIS ?

Otherwise, are there another ways to load tables from Oracle Spatial database to PostGIS ?

Any suggestions would be greatly appreciated!

Regards!
_______________________________________________
postgis-users mailing list
postgis-users <at> lists.osgeo.org
http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users
Tumasgiu Rossini | 29 Jan 13:42 2015
Picon

ST_BandPath() returns null or ô on out-db raster

Hi List,

I am experiencing a problem with outdb geotiff importation.

I am using the raster2pgsql utility :

raster2pgsql -R -a -F  \\fileserver\raster\419\napoleonien\*.tif catalog

On a particular set of geotiff ( wich are generated with the same routine as the others ), the command seems to work fine
but St_BandPath() output null when I'm trying to register the tiffs on a local server, and it output the character  'ô' on the production server.

On others set of raster, the band path is correctly stored.

What am I doing wrong ?



My configuration is the following :
Both Local and server ->
   PostgreSQL 9.3.5 installed with EntrepriseDB
   Postgis "2.1 USE_GEOS=1 USE_PROJ=1 USE_STATS=1"
   Win 7 64bit
"French_France.1252"
_______________________________________________
postgis-users mailing list
postgis-users <at> lists.osgeo.org
http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users
Guillaume ARNAUD | 28 Jan 09:16 2015
Picon

Get parallel some route.

Hello,

We use Postgre / Postgis database with ArcGIS Server and Desktop 10.2. All our data are inserted with keyword Postgis into our database.

The table that has the trouble is a route table with 292 linestring. I need these routes is not bunk. I try to use ST_OffsetCurve function to do this. I found on the help and on a ticket that we must have geometry which is simple. More than 250 isn't. Is there any solution to fix the problem or to bypass the problem .

Thanks for your work.

Greating.

ARNAUD Guillaume

Sorry for my poor english, I'm french.

"PostgreSQL 9.2.2, compiled by Visual C++ build 1600, 64-bit POSTGIS="2.0.4" GEOS="3.4.2-CAPI-1.8.2 r0" PROJ="Rel. 4.8.0, 6 March 2012" GDAL="GDAL 1.10.0, released 2013/04/24" LIBXML="2.7.8" LIBJSON="UNKNOWN" (core procs from "2.0.4" need upgrade) RASTER (raster procs from "2.0.4" need upgrade)"

OS : Windows Server Standard SP 2

--
Guillaume ARNAUD
Cellule SIGD
Direction de l'Informatique
Conseil Général de Tarn-et-Garonne
05-63-91-82-37
En interne, utilisez le Click To Call : 8237

_______________________________________________
postgis-users mailing list
postgis-users <at> lists.osgeo.org
http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users
cwright | 26 Jan 15:25 2015

Spatial statistics on LineString Z

Hi,
I’ve loaded a shapefile containing height information into PostGIS using
OGR2OGR.  The shapefile has loaded OK and the geometry all seems to be fine
in PostGIS, showing as a LineStringZ geometry.  When I run
st_estimated_extent on the table it fails with the error message ‘ERROR:
stats for “testlayer.shape” do not exist.  
When I run an analyze on the table it says "NOTICE: no non-null/empty
features, unable to compute statistics"

Running st_isvalidreason(shape) on the table says that all the geometry is
valid.

Is this a known bug/limitation with postgis?

We are running on:
PostGIS 2.1.5  64 bit
Postgres 9.3.5 64 bit
Windows 2012

Thanks

Colin Wright
Principal Developer
thinkWhere Ltd

--
View this message in context: http://postgis.17.x6.nabble.com/Spatial-statistics-on-LineString-Z-tp5007644.html
Sent from the PostGIS - User mailing list archive at Nabble.com.
_______________________________________________
postgis-users mailing list
postgis-users <at> lists.osgeo.org
http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users
Mark Wynter | 26 Jan 05:04 2015

Evenly distributing a point set


> 
> These points were created from a GPX file along a road journey so they crudely map to a line string

"Map matching" is the term often used to describe the process of assigning noisy Gpx points to underlying
road network.

> I was thinking compute the KNN distance for each point and remove those that have a KNN < a threshold (say 100 metres)
> 

Not sure what your data is like- 

Perhaps look at st_linesubstring where you can use generate series to produce a sequence of relative
positions 

> Is that the best way to go about it?**************

You may need to explore different methods - a method that works with your training dataset may not work so
great on other Gpx datasets that were collected under different conditions
Dave Barter | 25 Jan 12:54 2015
Picon

Evenly distributing a point set

If I had a table of points which are irregularly distributed and wanted to evenly distribute them what would be the best strategy and query?

These points were created from a GPX file along a road journey so they crudely map to a line string.

I was thinking compute the KNN distance for each point and remove those that have a KNN < a threshold (say 100 metres)

Is that the best way to go about it?

-- 
Dave Barter

Web: http://www.phased.co.uk
Email:dave <at> phased.co.uk
Twitter: <at> Citizenfishy
_______________________________________________
postgis-users mailing list
postgis-users <at> lists.osgeo.org
http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users
Ravi Kumar | 25 Jan 11:02 2015
Picon

Triger

Hi,
1 am using Gammu (http://www.ubuntugeek.com/wammu-mobile-phone-manager.html) to receive SMS of
location from OSMand ( http://osmand.net/ ).
2 Have written an sql to fishout the Long and Lat from the SMS and convert to easting and northing.

TRIGGER

Now need to write a trigger such that the operation is automated soon after an SMS is received from OSMand 

Pl help me write the trigger

This inturn will update a webpage for live information.

Thanx in advance

Ravi Kumar
Mark Wynter | 22 Jan 01:28 2015

Re: Checking Road Network Topology

Hi List

Interesting topic.  I’ve developed a solution for processing large scale road networks (OSM) using
techniques that strk and R?mi-C have mentioned.

The solution uses PostGIS for “breaking" and “nodeing” . GRASS for snapping and patching.

The PostGIS step uses a custom SQL function to break lines according to a set of thresholds and rules
- the function diligently deals with bridges and tunnels, and special cases such as motorways,
motorway_links, etc, to break lines where they need to be broken
- the function does not rely on PostGIS topology - just plain vector operations.
- the PostGIS step can be executed in parallel (if you tessellate the network first to produce “vector”
tiles)  - not dissimilar to the concept of Map Reduce.  For example, the whole of Australia (1.6M+ roads)
takes 12 minutes to process in PostGIS on a 32 vCPU AWS instance.  Scaling out to process even bigger road
networks is no problem because each tile can be processed independently, and stitched back together afterwards.

The GRASS step involves 5 operations
1) v.clean tool=snap # no bridges, no tunnels
2) v.clean tool=snap # bridges only
3) v.clean tool=snap # tunnels only
4) v.patch (steps 1-3)
5) v.clean tool=snap # on the patched layer, with a much smaller tolerance to that used in steps 1-3 to again
avoid say tunnel nodes inadvertently snapping to bridge nodes.

GRASS snapping and patching when done for whole of Australia in one go takes 48 minutes on the same 32vCPU
machine setup - but with 31 CPUs sitting idle :-((.   I could cut the GRASS time down massively if I were to
parallel process each vector tile.  The GRASS setup uses the PG Driver (PostgreSQL) to store the road
attribute tables and v.out.postgis -f to write the final vector geometries back to PostGIS.

Figuring out this process to produce a highly representative network topology has not been trivial.  The
workflow is scripted in bash, and uses psql, GNU Parallel.   I use QGIS Desktop 2.6 (multithreaded
rendering) installed on the processing server to visualise the PostGIS outputs.

This may not give immediate comfort to Stephan who may need something here and now.

Longer term, I’m happy to look at how I can contribute this solution to the PostGIS / OSM community - and to
see if I can pick up paid work in this space.   I’ve funded all of this myself (forgoing income for myself and
my family in the process).  I’ve also been developing OSM map matching techniques, where you process
“crowd sourced” GPS feeds to infer actual travel speeds along each road segment (time of day, day of
week etc) - which can then be used to inform service planning processes, and routing algorithms using
accurate information about local traffic conditions, congestion etc.

I’m open to any ideas, leads or offers of help.

Mark

On Wed, Jan 21, 2015 at 09:11:16AM +0100, R?mi Cura wrote:
> Hey,
> we previously discussed this matter of cleaning topology.
> Postgis topology is about iso topology storage and use, not cleaning.

That said, you can still use PostGIS for cleaning the data you're willing
to store in a topological model. It might need you to make your own
cleaning procedures, but we're happy to accept contributions :)

--strk;

Yeah sure , it was discussed on the user list :

http://lists.osgeo.org/pipermail/postgis-users/2014-November/039758.html

I personnaly tried the grass v.clean and it is fast and efficient (altough
the grass UI is terrible in my opinion)

Cheers,
R?mi-C
Joseph Spenner | 21 Jan 22:22 2015
Picon

Possible for ST_Intersects to not include the perimeter?

I have a polygon as an input, and I'm trying to find all polygons which share points, but not the perimiter points themselves.  Is this possible?

Real application:
  I have NWS polygons describing Thunderstorm Warnings, which are made up of counties within the state.  Some of those counties might be on the edge of the state.  I want to query my database to show me all Warnings which are in a supplied state.  However, if I query the adjacent state which borders the counties from the first state, I get those Warnings because they share the same parimeter points.

Is there a way to construct a query which will not return anything if the only points in common are the perimeter values themselves?

I tried a few variations on ST_Intersects/ST_Covers/ST_Contains/ST_Overlaps, supplying various AND/OR and TRUE/FALSE combinatins, but I can't seem to get the right combo to accomplish my task.

Any help would be great.

Thanks!

Regards,
Joseph Spenner


_______________________________________________
postgis-users mailing list
postgis-users <at> lists.osgeo.org
http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users
Rémi Cura | 21 Jan 14:17 2015
Picon

Re: Checking Road Network Topology

Yeah sure , it was discussed on the user list :

http://lists.osgeo.org/pipermail/postgis-users/2014-November/039758.html

I personnaly tried the grass v.clean and it is fast and efficient (altough the grass UI is terrible in my opinion)

Cheers,
Rémi-C

2015-01-21 14:05 GMT+01:00 Miller, Stephan <smille12 <at> harris.com>:

Thanks.   I will look into GRASS.

 

Can you send a pointer to the blog where this topic was discussed?

 

Thanks,

 

Steve

 

From: Rémi Cura [mailto:remi.cura <at> gmail.com]
Sent: Wednesday, January 21, 2015 3:11 AM
To: PostGIS Users Discussion; Miller, Stephan
Subject: Re: [postgis-users] Checking Road Network Topology

 

Hey,

we previously discussed this matter of cleaning topology.

Postgis topology is about iso topology storage and use, not cleaning.

There are dedicated and powerfull tools for this (like Grass).

Cheers,
Rémi-C

 

2015-01-20 21:26 GMT+01:00 Sandro Santilli <strk <at> keybit.net>:

On Tue, Jan 20, 2015 at 07:45:47PM +0000, Miller, Stephan wrote:

> I am attempting to validate the topology of
> a dense road network ( 1.2 Million roads)
...
> Everything appears to be flagged as an error; the ValidateTopology call returns nothing.

You mean _no_ record got imported ?
Can you share the dataset ?
What's the output of "SELECT postgis_full_version()" ?

--strk;

  ()   Free GIS & Flash consultant/developer
  /\   http://strk.keybit.net/services.html
_______________________________________________
postgis-users mailing list
postgis-users <at> lists.osgeo.org
http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users

 


_______________________________________________
postgis-users mailing list
postgis-users <at> lists.osgeo.org
http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users
Miller, Stephan | 20 Jan 20:45 2015

Checking Road Network Topology

All –

 

I am attempting to validate the topology of a dense road network ( 1.2 Million roads) modeled after http://blog.mathieu-leplatre.info/use-postgis-topologies-to-clean-up-road-networks.html from July 2013.  The code is given below:

 

 

-- HC_RoadTopologicalCheck

-- Drop the existing topology

 

SELECT topology.DropTopology('roads_topo');

 

SELECT topology.CreateTopology('roads_topo',4326, 0.000001, true);

 

SELECT topology.AddTopoGeometryColumn('roads_topo', 'fgcm', 'transportationgroundcrv', 'topo_shape', 'LINESTRING');

--UPDATE transportationgroundcrv SET topo_shape = topology.toTopoGeom(transportationgroundcrv.shape, 'roads_topo', 1, 1.0);

 

-- Encapsulate the UPDATE inside code that checks for errors

DO $$ DECLARE r record;

BEGIN

    FOR r IN SELECT * FROM transportationgroundcrv loop

        BEGIN

            UPDATE transportationgroundcrv SET topo_shape = topology.toTopoGeom(transportationgroundcrv.shape, 'roads_topo', 1, 1.0)

            WHERE r.objectid = r.objectid;

        EXCEPTION

                WHEN OTHERS THEN

                RAISE WARNING 'Loading of record % failed: %', r.objectid, SQLERRM;

        END;

    END LOOP;

   

END$$;

 

Select * from ValidateTopology('roads_topo');

 

I am getting everything passing to the exception statement and all the records are being flagged with

 

WARNING:  Loading of record 791456 failed: SQL/MM Spatial exception - curve not simple

 

Intermittently, I am getting the following error also.

 

WARNING:  Loading of record 792591 failed: Spatial exception - geometry intersects edge 69612

 

 

Everything appears to be flagged as an error; the ValidateTopology call returns nothing.

 

Any insights or suggestions would be appreciated.

 

Steve

 

 

_______________________________________________
postgis-users mailing list
postgis-users <at> lists.osgeo.org
http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users

Gmane