PostgreSQL is an open source object-relational database system.

learn more… | top users | synonyms (1)

1
vote
0answers
54 views

refine nearest_node with radius-distance ( ST_Distance, ST_DWithin )

i am a hobby-IT-person and currently stuck with some simple problem, maybe somebody can help me: This is my working POSTGIS-function, source by underdark: ALTER TABLE distance_graz ADD COLUMN ...
1
vote
1answer
115 views

Join two tables completely on geometry (postgis)

I have two tables with the same street network as geometry (but not a same id). What I want to do is to join both tables with all attributes to one for all geometries which overlap exactly. ...
2
votes
0answers
36 views

Creating lines from lonely nodes in a linestring table to closest point on another linestring table

I want to segmentate a country (denmmark) by using barriers such as larger roads, railroads and the border/ coastline as the delimiting lines of the polygonise function in QGIS. But as most roads and ...
2
votes
0answers
41 views

Changing border style in OpenStreetMap?

I am trying to figure out how to change OpenStreetMap country borders color/thickness. I've set up a tile server using OSM data, PostgreSQL & Mapnik. The default mapnik style is used. There ...
3
votes
2answers
319 views

Is there a way to change ST_asGeoJSON return values?

The function ST_asGeoJSON returns something like: "st_asgeojson": { "type":"MultiLineString", "coordinates":[ [ [-3.13512629299993,39.7174353130001], ...
3
votes
2answers
95 views

PostGIS does not find intersection between line and polygon

I have a line and polygon: I would like to programmatically figure out whether they intersect or not. So, I do: select ST_Intersects ( ( select ...
0
votes
0answers
34 views

Number of rows returned via `shp2pgsql` for a given shapefile

With the shp2pgsql tool, I see it converts a shapefile into a SQL statement. Upon closer examination, does the SQL generated ALWAYS contains 1 insert statement for 1 row? Or are there instances where ...
1
vote
1answer
30 views

counting values in an update statement postgres [closed]

I have a repetitive method I want to execute and I expected underneath script to work. I found out the count() function is not allowed in postgres update statements. What is the most logical and ...
2
votes
2answers
135 views

QGIS Web Client - postgreSQL Layers wouldn't shown

since updating my QGIS to version 2.8.3 the postgreSQL Layers wouldn't shown in the QGIS web client. In the Desktop version the project works very well I only can see the projecttitle and the groups ...
1
vote
1answer
73 views

OpenStreetMaps: what are the !pixel_width! and !pixel_height! in the queries

The Github repository containing the CartoCSS styling for the OpenStreetMaps dataset also defines queries for the different layers (see this file). An example of such a query is SELECT way, ...
0
votes
2answers
59 views

Finding vertex in comon with most polygons in PostGIS?

I have a table with polygon geometry, in this case representing counties. I'm trying to figure out how to write a query that returns vertices sorted by the number of polygons that share that vertex. ...
0
votes
0answers
49 views

Receiving -infinity in geoserver wfs GetFeature result

I'm currently using: - Geoserver 2.7.2 - PostgreSQL 9.4.4 - PostGIS 2.1.9 When I submit the following wfs GetFeature request: ...
1
vote
0answers
50 views

PCA on correlation matrix in PostgreSQL

Does anyone know how to perform PCA using PL/pgSQL in a PostgreSQL function, considering correlation matrixes of potentially high order? Maybe a specific library for this problem... Thanks.
1
vote
1answer
73 views

Applying OSM z-order after osm2pgsql with qgis

I've trying to find a solution to applying the OSM z-order in qgis after using osm2pgsql. Everything is working perfectly, until I add the each layer to the canvas, where the z-order is not being ...
0
votes
0answers
64 views

Fulltext Search with Spatial Search with pagination

We tried to use postgresql+postgis to save our spatial data with points/lines/polygons. Though the postgresql have a built-in full-text search engine, we have to use lucene for some reasons, and ...
1
vote
0answers
73 views

Gisgraphy importer crashing, possibly due to JDBC connections problem. Is there a fix? [closed]

I've been trying to install gisgraphy for the past few days. The setup has went well, but the software crashes while importing data. I'm only importing data for a single country so it's not overly ...
1
vote
0answers
56 views

raster2pgsql stops at line 53 at RETURN

I am importing some very large rasters (in the tif format) into Postgres SQL (9.3) using raster2pgsql on the command line. Most of the rasters imported fine, some had to be converted to the img format ...
1
vote
1answer
37 views

PostgreSQL - PostGIS linkup error

I have a database that I viewed in QGIS using PostGIS. I managed to link them up using the correst host, database combination last year. I am now revisiting the work and am unable to link them ...
0
votes
0answers
45 views

pgr_dijkstra doesn't return when cost values are low

I'm using a different cost column in pgRouting, and passing it to pgr_dijkstra but it returns 0 rows. I analyzed that column and contains some 0 and values in scientific notation (1e-6) and also ...
3
votes
1answer
204 views

Postgres, loading raster into db and visualize with qgis

I'm trying to import a TIF raster into a Postgres db using this command: Raster2pgsql -s 26591 -I -C -e -Y -F –N -M C:\Users\Ivn\Desktop\Postgre_HOME\scratchmres1.tif -F | psql -U postgres -d ivndb ...
2
votes
1answer
64 views

Generating Shapefiles from PostGIS as a Plugin

I used to generate shapefile from PostgreSQL database that is installed with PostGIS tables as templates (geometry_columns and spatial_ref_sys) Shapefiles generated just fine using the following ...
0
votes
1answer
44 views

Using ST_Extrude in Postgres 9.2, with PostGIS 2.2.0

I'm trying to use the function ST_Extrude() in Postgres 9.2. I enabled the PostGIS 2.2.0 extension and the PostGIS_SFCGAL extension (which carries the ST_Extrude function). I can see the function in ...
1
vote
1answer
40 views

Keep empty GRID Cells in PostgreSQL (after join)

I want to join a 100*100m Grid with a point layer. The points contain the number of people living in the grid cell. There are some cells with no polulation, which I want to keep with the Attribute ...
2
votes
1answer
185 views

Installing Postgis 2.2.0 on Windows?

I'm trying to get the newest version of Postgis (2.2.0) up and running with Postgres 9.4. I'm using Windows and the only installation manuels I found, are Linux-only. Can anybody point me in the ...
1
vote
2answers
109 views

Edit Geometry with digitizing tools in QGIS of a postgis layer

I have a multiline PostGIS layer added to my QGIS 2.10.1 Pisa. I'm able to add features using the digitizing tools, but I'm not able to use the delete or node tool. It remains gray even if I'm in ...
0
votes
1answer
54 views

Error in using 'psql' from osm2po [closed]

I am following this direction to make network dataset from OSM data to use ArcGIS network analysis (by using osm2po and psql)(http://revenant.ca/www/postgis/workshop/loadingdata.html). So far so good ...
2
votes
1answer
22 views

Multi-string data-set without z layer, applying ordinal values as a workaround

I'm new to GIS and have been learning a lot over the last few weeks, however, I've finally come to a point where I could use some help. I'm working with the pdx_streets data-set from ...
0
votes
1answer
280 views

Install Postgresql / Postgis with SFCGAL

I need to access ST_StraightSkeleton() function. At the moment i have a Postgresql instalation PostgreSQL 9.3.4 compiled by Visual C++ build 1600, 64-bit installed via EDB with Postgis 2.1.7 on a ...
1
vote
1answer
91 views

How to make a change in the_geom_webmercator visible in the_geom in CartoDB?

I am having some trouble with the the_geom column and the the_geom_webmercator column in CartoDB. I have some SQL (which I modified from http://bl.ocks.org/andrewxhill/8695515) in which the ...
2
votes
0answers
45 views

How to clean the postgres instance while setting cartoDB locally in ubuntu

I am following https://github.com/danseely/cartodb-install/blob/master/DEV-INSTALLATION.md for setting cartoDB locally in ubuntu12.04. While installing cartoDB-postgresql, the second install check ...
2
votes
2answers
281 views

Postgis ST_Intersects performance

I am selecting a lot of polygons within a gigantic polygon by using the postgis function ST_Intersects(). SELECT * FROM data.polygons AS poly, data.gig_polygon AS gig ...
0
votes
1answer
33 views

How Update one table row with combare with the other polygon table using their geom in SQL Server

a-> points b -> Polygon i want to update city trow in table a with compare b of geom in SQL server this is the PostgreSQL Query update a set city=b.name from b where st_contains(b.the_geom, ...
2
votes
1answer
185 views

How to save postgres table to kml without losing attributes using python

I am saving the postgres table loaded in QGIS to kml. qgis.core.QgsVectorFileWriter.writeAsVectorFormat(aLayer, myFile, "ogr", projDest, "KML") But the output file does not have the attributes. So ...
2
votes
0answers
105 views

How to export ESRI Geodatabase format .gdb from PostGIS

I found several posts explaining how to import a .gdb file into PostGIS. Is exporting also possible? For example using ogr2ogr to generate a .gdb file from query result.
1
vote
1answer
130 views

Load the postgis layer/table to qgis canvas using python

I want to load the postgis layer to qgis canvas. I am using the following code but it is not loading the layer in canvas. sql = "(SELECT * FROM table WHERE id = 20)" uri = QgsDataSourceURI() ...
2
votes
1answer
35 views

How to solve the error: query must return columns 'id', 'source', 'target' and 'cost' in postgres 9.3

SELECT seq, id1 AS source, id2 AS target, cost FROM pgr_kdijkstraCost( 'SELECT gid, source, target, cost FROM road_network', 26, array[1055,2013], false, false ); I am getting the ...
2
votes
2answers
119 views

I import map from file osm.pbf using osm2po. Why the low speed function call pgr_dijkstra in database Postgresql?

Good day! I import map from file russia.osm.pbf using osm2po. Why the low speed function call pgr_dijkstra in database Postgresql (>2 minute)? My steps: java -Xmx6g -jar osm2po-core-5.0.0-signed.jar ...
2
votes
1answer
116 views

pgrouting calculate length of shortest travel time route

I currently use the pgr_kdijkstraCost() function to calculate the travel times of a few origins and destinations. But I would also like to return the distance length of these trips. Is this possible? ...
1
vote
1answer
169 views

PostgreSQL Database Connection Issue

I have two separate computers, each of which are running ArcGIS Desktop 10.3.1 and PostgreSQL and PostGIS 2.1.7. The only difference is one is running PostgreSQL 9.3.4, the other is running 9.3.5. I ...
1
vote
1answer
54 views

Clustering by property

I have a number of points with a "time" property. I need a procedure that, given a total time, results in a number of clusters in which the sum of the time property do not exceed the given total time. ...
1
vote
0answers
52 views

How to scatter all points at identical locations in CartoDB?

I am working on a project in which data points are often at the same location (e.g. Rome). This poses a problem because the data points then overlap and cannot be clicked on. I found some SQL at ...
2
votes
2answers
89 views

point returned from ST_LineLocatePoint returns false in ST_Contains

I am using postgis's ST_LineLocatePoint to find out the closest point on a LineString to the given Point, and using ST_LineInterpolatePoint to extract a Point from the returned float number.(as ...
1
vote
0answers
41 views

Resuming an osm2pgsql import to postgres [closed]

I was doing an import of the north american data with osm2pgsql using sudo ./osm2pgsql -c -S /home/osmNA/nfs/osmNA/osm2pgsql/default.style --slim -d addressNA --flat-nodes flat-nodes.bin ...
1
vote
0answers
58 views

Why is the geometry changing slightly when I import shapes into a postgresql database with postgis?

I recently posted this question when first trying to import data from a file geodatabase into a postgis database: how can I maintain curves when converting from geodatabase to postgresql using og2gui ...
1
vote
1answer
62 views

PostgreSQL equivalent of ArcGIS “Eliminate” function?

I am looking to eliminate small slivers in a shapefile dataset using PostgreSQL. What would work to dissolve features < 0.1 hectares into the neighbour with the longest shared boundary (1) or the ...
0
votes
1answer
115 views

Geoserver max connection

I, im creating a application to display some data with geoserver and postgres database. My application is working fine when i displays 5-6 layer of data one by one, after that, the next layer I try to ...
2
votes
1answer
170 views

Alternative approach for loading osm2pgsql data

I am slowly going insane trying to successfully complete an OSM data import and I would like to find an alternative approach. What I'm attempting is frustratingly simple yet fails repeatedly. ...
0
votes
2answers
104 views

Modifying “pg_hba.conf” file to listen a dynamic address

this is the sequel of "Modifying "pg_hba.conf" file to listen local TCP/IP conection" I manage to modify pg_hba.conf to listen other PCs and allow them through the firewall, but my problem now is ...
1
vote
1answer
59 views

Finding out the extent of the default zoom levels of open layers

I'd like to reproduce the zoom levels of openlayers in postgis. I am not used to working with Javascript and am having difficulties to find out the exact extent in meters of the default zoom levels of ...
0
votes
1answer
92 views

function st_length3d(geometry) does not exist

Whenever I execute this query: SELECT ST_Length3D(ST_GeomFromEWKT('LINESTRING(0 0 0,5 0 3,5 10 5)')); Postgresql is throwing an error: ERROR: function st_length3d(geometry) does not exist SQL ...