Tag Info

Hot answers tagged

3

Postgis is an extension to Postgres, rather than a stand alone application, that provides a spatial data type to Postgres, and provides numerous spatial functions that operate on geometry(ies). Spatial indexing, which you will surely need to find n closest points efficiently, is implemented as an extended R-tree, but the indexing mechanism comes from ...


2

You have two issues mixed. First: pgRoute, uses precreated topology based on LineString geometry. Such geometry doesn't have direct links on osm Nodes. You could get geospatial points for built route, not osm Nodes. To get Nodes, you could build an index among osm Nodes geometries and perform distance lookup. In other words, select all nodes which are not ...


2

The slow part of your query is probably the ST_Contains(ST_Transform(way,4326),..), postgres is having to transform every way in the table to run this query. There are two options to fix this: If the main purpose of your database is this type of query it might be more efficient to reimport and store the geometries as EPSG:4326 instead of the ESPG:900913 ...


2

The query will take quite a while, for sure, w/ 250M points (perhaps too long). I'm assuming here that your points are in a metric coordinate system. Looks like this: SELECT a.gid, count(*) FROM pts a JOIN pts b ON ST_DWithin(a.geom, b.geom, 100) WHERE a.gid != b.gid GROUP BY a.gid;


2

There is ST_LineMerge function http://postgis.net/docs/manual-2.0/ST_LineMerge.html You could try to serve all your rivers network as one MultiLineString ST_LineMerge(ST_Multi(St_Collect(geometry))) The result is also a MultiLineString with segments sewed together. So after ST_LineMerge you could get sewed segments via ST_Dump.


2

You're almost there, you need to declare the sequence as a dependent of the table. ALTER SEQUENCE polygon2_gid_seq OWNED BY polygon2.gid; Now the sequence will cascade drop with the table.


1

One approach, is to use a spatial self join, so you compare all points with all others (you set a.id != b.id, to avoid comparing a point with itself), then use ST_DWithin for a radius search (replace 10 with you own number). SELECT count(b.label), a.label, a.geom FROM points A, points B WHERE ST_DWithin(a.geom, b.geom, 10) AND a.id != b.id GROUP BY ...


1

You need to use osm2psql for example, if you want to import data, that is not (road) network data. osm2pgrouting only handles linestring geometries and will not work with point data.


1

You need to also grant privileges for the table's sequence too: GRANT USAGE, SELECT ON SEQUENCE topology_id_seq TO public; Or if you want to provide liberal privileges for all sequences: GRANT USAGE, SELECT ON ALL SEQUENCES IN SCHEMA public TO public;


1

You get this error when there are no results, so the query returns 0 records, and row[0] does not exist. Using another postgres query method you can avoid this error: $result = pg_exec($dbconn, $query); $numrows = pg_numrows($result); // Loop through rows in the result set for($i = 0; $i < $numrows; $i++) { $row = pg_fetch_array($result, $i); ...


1

CREATE TABLE test_points as SELECT ST_Intersection(a.geom, b.geom), Count(Distinct a.gid) FROM roads as a, roads as b WHERE ST_Touches(a.geom, b.geom) AND a.gid < b.gid /* !!! Changed "!=" for "<" */ GROUP BY ST_Intersection(a.geom, b.geom) ; If line A (id 1) ...


1

You can use the following query to union the geometry, find all nodes and split the lines: SELECT row_number() OVER() new_id, geom FROM (SELECT (ST_Dump(ST_Node(ST_Union(the_geom)))).geom geom FROM multilines) a Then you must play a little bit to retrieve the original attributes by joining the previous table on "contains" or "within".


1

as you can see in your relation_members table, the member_role belonging to your id ( 57582 ) is 'R' <<-- Relation. So your id (108786) contains another relation. Check: select * from relation_members where id = 57582; to see if there is another relation nested within this one. Your can select the id from the nodes or way table if the corresponding ...


1

Did you try to check if this id (57582) is present in your table 'relations' ? I'm not really used to the pgnapshot schema but i guess it should be referenced itself as a relationship in your database ? Hope you will find (or already have) a solution to your answer!


1

If you want to extract the few addr:* tags attached to streets, the current version might help. But you'll need Java to overwrite some standard behavior. Implement your own WayTagResolver or extend the Default one. There is a "meta"-attibute which can be populated withr custom data. Nevertheless, GeoCoding is one of the next steps I'm going to implement. One ...


1

I am not sure but minumum bounding rectangle function may still be missing from PostGIS http://lists.osgeo.org/pipermail/postgis-users/2012-June/034419.html Meanwhile you can use OpenJUMP which can read and write data from PostGIS.


1

I followed this guide by a Duncan Golicher: https://duncanjg.wordpress.com/2012/11/20/the-basics-of-postgis-raster/ I was using tiles with an automatic setting originally, but I reset the tiling to 100x100 cells per row and then included the pyramids as shown in the guide like so: raster2pgsql -s 3161 -d -C -I -M -l 4 D:\PostGIS_data\dem.img -t 100x100 ...


1

You should add a PostGIS extension, and use the geometry type instead. It will handle 30000 features easily, and work with many GIS systems (e.g. QGIS). You can always extract the WKT equivalent from a geometry column using ST_AsText. Here's an example workflow, assuming it's a table in postgres --Say you have a table described in your question CREATE TEMP ...


1

Your Reader and Writer types are incorrect. They should be readers.las and writers.pgpointcloud, respectively. The name change happened here: https://github.com/PDAL/PDAL/commit/b7cde7e1fb13f28db1de7797dbb31392db1f7dfc. You can run pdal --drivers to get a complete list of drivers supported on your system.



Only top voted, non community-wiki answers of a minimum length are eligible