Questions tagged [postgresql]

PostgreSQL is an open source object-relational database system.

590 questions with no upvoted or accepted answers
Filter by
Sorted by
Tagged with
2
votes
0answers
99 views

Is there a function to obfuscate a PostGIS point or geometry?

I am looking for a function, perhaps a Perlin noise implementation or something similar, for PostgreSQL/PostGIS. Does such a thing exist in core PostGIS? If not, does such a function exist an ...
2
votes
1answer
686 views

Cache lookup failed for function 1 while enabling postgis on postgres 10

I am trying to enable PostGIS on a PostgreSQL 10 database on Windows. Earlier it used to work fine, but now I am getting an error 'cache lookup failed for function 1' on enabling the extension. I am ...
2
votes
0answers
253 views

osm2pgsql --append super slow (node 2k/s vs 1500k/s for --create)

I'm working with multiple pbf files (one per administrative region). I thought I would import them one by one in postgis, with: osm2pgsql --proj 4326 --hstore-all --database gis first.pbf --style ...
2
votes
0answers
48 views

PostGIS/QGIS visualization with strange characters in attribute table

I loaded a shapefile into a PostGIS database. When I visualized it in QGIS I noticed that in the attribute table, some fields with integer data type, are displayed in a strange form. However when I ...
2
votes
0answers
246 views

Unable to show geometry in virtual layer qgis

I'm trying to create a virtual layer starting from a municipality layer and from a table of attributes, both stored in a Postgresql DB. So I've done a join like: *SELECT munic, country_of_origin, ...
2
votes
0answers
345 views

Improve the speed for ST_DWithin

I need to get the outer polygon of the LSOAs within 10 miles of each postcode centroid. There are 180k LSOA polygons in table1 and 2.6 million postcode centroid points in table2. I have created 2 ...
2
votes
0answers
193 views

Connecting to PostgreSQL using QGIS using a VirtualMachine?

I installed Postgresql in Windows server 2012 Essential on Virtual Machine. Everything works ok , but when i try to use QGIS Desktop which is on desktop installed to access to Postgresql-data , the ...
2
votes
0answers
39 views

Is there an equivalent “why-upgrade PostgreSQL” tool for PostGIS?

I rarely keep up with the PostgreSQL and PostGIS releases and it can a challenge deciding on when to do so for the major/minor releases. The why-upgrade site (http://why-upgrade.depesz.com/show?from=...
2
votes
0answers
316 views

Using function in pgPointCloud extension to PostgreSQL with PostGIS

I am using the pgPointCloud extension to PostgreSQL and have created a table of blocks/patches of points in a point cloud. Now, I am just trying to run a few of the functions in the pgPointCloud/...
2
votes
0answers
139 views

PostGis st_dwithin super slow when inserting

I am trying to insert the newly obtained data from st_dwithin into a new table. However, it has been running since 1 day and I still can't get any data into the new table. Can i know what is the ...
2
votes
0answers
1k views

Save Leaflet drawn features with attributes to PostGIS

Goal: In order to have a fully interactive map, I would like to be able to save some drawn features (in a second step with some attributes that may be grabbed through an input mask form or pop-up) on ...
2
votes
0answers
105 views

Loading raster to R from PostgreSQL via pgGetRast takes a lot of time

I wrote R code in R Studio to load a raster (stored in PostgreSQL) to R. The database is stored here: C:\Program Files\PostgreSQL\ The following versions are installed on the computer: R version 3.4....
2
votes
0answers
249 views

ST_Voronoi is creating invalid geometries

I have a workflow where I am creating Voronoi polygons from a series of points. However since the polygons on the exterior extend really far out I have clipped them and then joined them back to points ...
2
votes
0answers
302 views

geopandas dissolve versus postGIS postgresql aggregated groupby dissolve

I have a geodataframe for which I want to dissolve the geometry based on an attribute (fao_id). In geopandas this is easy as gdf.dissolve(by="fao_id", aggfunc="first") but unfortunately this fails due ...
2
votes
0answers
2k views

Ogr2ogr: ERROR 1: Unable to find driver `PostgreSQL'

I need to import geojson files into a local PostgreSQL9.6 database on OSX system. ogr2ogr -f 'PostgreSQL' PG:"dbname=crossmap user=duccioa" "multigraph.geojson" -nln paris.multilayer Ogr2ogr gives ...
2
votes
0answers
587 views

Failed…error: bind message supplies 3 parameters, but prepared statement “” requires 1

I am using node to capture gps data from my devices after every 30 seconds, i want to store the data into a table but when executing the query statement it throws an error Failed.........error: bind ...
2
votes
0answers
367 views

Importing GeoJson data to postgresql using ogr2ogr command

I am using following ogr2ogr command to import GeoJson data to Postgres DB. The command runs successfully but the geometry column is null. ogr2ogr -f PostgreSQL PG:"dbname=test user=postgres ...
2
votes
0answers
251 views

Can't connect Docker PostGIS container to QGIS

I recently started experimenting with Docker and PostGIS following this documentation. I have a locally installed version of QGIS which I am attempting to connect the default Postgres database to. ...
2
votes
0answers
219 views

pgRouting: Find source/target ID of edge closest to points in a separate layer for many-to-one routing

I have a small road network which I have extracted from OSM and prepared for routing using OSM2PO. I also have a separate layer of points (postcode centroids). I wish to compute many-to-one routing ...
2
votes
0answers
291 views

How can I split a polygon set with a line set using PostgreSQL recursive queries?

I have a simple polygon layer GEOMETRY(POLYGON,4326) and a simple line layer GEOMETRY(LINESTRING, 4326). I want to find all split areas. I am using PostGIS 2.0. I am using following PL/pgSQL ...
2
votes
0answers
137 views

Shape index, compactness and assymetry index with PostgreSQL /PostGIS

I am working on automated land parcel identification and would like to extract road/river/thin and linear-like polygons from a massive dataset. For now, the only index I can get some answers from is ...
2
votes
0answers
603 views

PostgreSQL/PostGIS Metadata - with QGIS

I'm stuck trying to get metadata for vector-layers into a PostgreSQL database with PostGIS-Extension. The ESRI-Environment allows to define metadata (ISO, FGDC, INSPIRE,...) and saves this information ...
2
votes
0answers
119 views

Create PostGIS trigger that splits lines

I have a table with LINESTRING where one condition is for lines to be composed of only 2 vertices. I want the user to be able to create, or import lines with more than 2 vertices, but upon feature ...
2
votes
0answers
231 views

Creating form in QGIS?

I have a PostgreSQL database wich containes a table with type point an 4 tables without geometry. I created foreign keys to garantee the relations between those tables. I migrated my db via postgis ...
2
votes
0answers
199 views

postgis raster to postgres database

hi Im trying to access my raster file in QGIS by connecting postgresql database. I can see my raster in qgis but I get this error : Geçersiz Katman: GDAL provider Cannot open GDAL dataset PG: ...
2
votes
0answers
206 views

PostGIS SQL Editor plugin - path to pgsql2shp

I'm new to PostGIS/QGIS, and I was looking at using the PostGIS SQL Editor plugin (v.2.0.0) instead of the OpenJump tool recommended in PostGIS in Action 2nd ed. However, I am having problems ...
2
votes
0answers
139 views

Missing PostgreSQL driver in rgdal: a long and sad story

This issue has been discussed few times and any of the read posts have not been the ultimate answer for my case: I'm trying to load data from a PostGIS database into the R Console. While many ...
2
votes
0answers
196 views

Is it possible to have postgres9.6 and postgis 2.3 on ubuntu 14.04?

I want to upgrade/install postgres9.6 and postgis2.3 on trusty but I cannot find a way... Has someone succeed on that? Ps. this repo sudo sh -c 'echo "deb http://apt.postgresql.org/pub/repos/apt/ $...
2
votes
0answers
758 views

OSM data as Postgresql dump

Does anywhere host the OSM planet DB as a Postgresql dump? I have tried using osm2pgsql but I have limited processing capabilities so it is never finishing. I do not have bandwidth limitations so ...
2
votes
0answers
141 views

Is it possible to perform a join on the fly within OpenLayers, to join a filtered tabular service to a polygon WFS layer?

We have around 2 million species observation records we want to display to users in an OpenLayers-based front-end. I created a VIEW in our PostgreSQL database that summarizes these records by species,...
2
votes
0answers
250 views

Merging adjacent and overlapping polygon from different sources with Postgis

I have three different sources in my PostGIS database that I would like to combine in a materialized view to make a single one. I want to merge all the adjacent and/or overlapping polygons to make a ...
2
votes
0answers
127 views

Memory issue after ST_MakeValid()

After running a ST_MakeValid() on a table with about ~400k records, I receive the following error: update "parcel15B" set geom = ST_MakeValid(geom) ERROR: could not extend file "base/16390/655718":...
2
votes
0answers
117 views

Not rendering new data after second import?

Trying to create a slippy map using apache2, renderd, and postgresql. I started with just one state and used osm2pgsql to import it. I got everything working great and decided to scale up to North ...
2
votes
0answers
206 views

PostGIS, PostgreSQL and Solaris

I need to install PostGIS on Solaris, but at the moment of installing I have this error: database=# create extension postgis; ERROR: could not load library "/opt/csw/lib/64/postgresql/93/...
2
votes
0answers
140 views

Importing multiple raster images in PostGIS

I have several raster images for one area and I am trying to import these multiple raster images in PostGIS. Is there a efficient way to import these raster images at once?
2
votes
0answers
2k views

How to import NetCDF file to PostgreSQL

I looked at the other similar questions posed and none of them seem to be answered. Basically I have a NetCDF file with multiple subdatasets and from what I've read, I should be converting it to a ...
2
votes
0answers
99 views

Exported OSM database do not contains node names

I've used osm2pgrouting tool to export data from osm file to my postgresql database. Now I have database with this schema: osm_node: -node_id -osm_id -lon -lat -numofuse -the_geom ...
2
votes
0answers
362 views

PostGis - st_union polygons, keep sum of attribute

There are multiple My current query is: SELECT (ST_Dump( ST_Union( way ) )).geom AS way, 10000 AS way_pixels FROM ( SELECT way, way_pixels, ...
2
votes
0answers
38 views

PostGIS - raster - bit operations

I have a raster data in the database in separate rows. I would like to select 2 rows and do a bit operation with them (like |, &) and store the result in memory as a query output. Is this possible?...
2
votes
0answers
71 views

How to count near intersecting polylines and merge them?

I have a dataset with several polylines created by GPS points of car trips. I am trying to create a simple heatmap where roads with more traffic (where polylines intersect) get darker. I already ...
2
votes
0answers
218 views

How to add fields to postgis layer loaded in qgis using python

I have loaded a layer from Postgis in qgis and I am adding the fields to the layer using the follo code snippet: canvas = qgis.utils.iface.mapCanvas() aLayer = canvas.currentLayer() field_name = "...
2
votes
0answers
79 views

PostGIS - ST_SetValues is slow

I have large data in my C++ program (up to 3000x3000). I have connection to PSQL with PostGIS. Now I need to store my raster data (greyscale images) in DB raster. I am using ST_SetValues for this and ...
2
votes
0answers
682 views

PostGIS Svg Projection on Google Map

Context I have a database with spatial data such as areas and points. Some of the areas in my db are coming from OpenStreetMaps, representing some regions. (Which are kinda small at earth's scale, ...
2
votes
0answers
271 views

update query with st_intersects() is not finding results

So I am trying to identify which items from the requests table fall into some particular district from the district table using postgis. Basically, I am writing an UPDATE query that will take the ...
2
votes
0answers
327 views

TopoGeometry commands with postgres are really slow

After messing around quite a while, I was finally able to get a topogeometry converted from my geometry table following the example of toTopoGeom. I have the topology extension, with a topology schema ...
2
votes
1answer
84 views

Issue with GeoJson data format fetching from PostgresQL

I am trying to display the GIS data on the Google map using PostgreSQL. I have few Shapefiles, I imported those shapefile into PostgreSQL. Now I am trying to get this data using this query. After ...
2
votes
0answers
103 views

How to display join query results on a sublayer in CartoDB.js application?

I'm working with my first CartoDB.js application. I created a visualization on the app using cartodb.createVis('map', visjson_url, opts); The app has two layers, base map and data layer. the data ...
2
votes
0answers
157 views

Efficient processing of ST_ValueCount Results in Postgresql

General Problem: I have a table of rasters. The rasters are classified MSI images, so each pixel is an integer indicating the class of the pixel. For a number of regions, I am querying pixel counts in ...
2
votes
0answers
77 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
93 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 ...