Questions tagged [postgresql]

PostgreSQL is an open source object-relational database system.

558 questions with no upvoted or accepted answers
Filter by
Sorted by
Tagged with
0
votes
0answers
112 views

Error restoring spatial database. ERROR : could not load library “/builds/postgresql-9.6.3/lib/rtpostgis-2.3.so”

I am trying to restore a backup of a database. I am running postgresql-9.6.3 with postgis-2.3.3 I run the following restore command .../myw_db new_db restore /path/to/database.backup I get the ...
0
votes
0answers
2k views

pgsql2shp - fe_sendauth: no password supplied

I am at a total loss here. I receive the error fe_sendauth: no password supplied when trying to execute the following pgsql2shp cmd. pgsql2shp -f folders/San Bruno/San Bruno.shp -h localhost -u ...
0
votes
0answers
58 views

Generating bus network segments between stations

I have a network I downloaded from OpenStreetMap. I created bus station points on it and I want to create segments between each two successive stations, each segment with a start_station & an ...
0
votes
0answers
202 views

raster2pgsql error while loading

I have done that many times and it usually works but I'm facing a problem now that I do not understand. I'm trying to load a raster (.tif) with a EPSG4326 and it is a one band file in my database from ...
0
votes
0answers
112 views

Connection Error on a PostgreSQL x64 9.5 database from ArcGIS Pro 1.3.0

I have the following PostgreSQL database named "cfa_fem_gc": CREATE DATABASE cfa_fem_gc WITH OWNER = gc ENCODING = 'UTF8' TABLESPACE = pg_default LC_COLLATE = '...
0
votes
0answers
69 views

Inserting geometry in ArcSDE table?

I have a test point table that created via ArcMap create feature class tool. And I am using ArcGIS 10.5 and PostgreSQL 9.5 database. I am using PG_GEOMETRY. I want to insert a sample point using sql ...
0
votes
0answers
136 views

Comma separated field in a PostgreSQL (PostGIS) SELECT query?

I've polygons and points in a PostGIS database. Let's say there are red, blue, green and yellow points. This information is saved in a field named 'color'. What I want to do is to select all polygons ...
0
votes
0answers
103 views

Change of data type after exporting from PostgreSQL to shapefile

I had loaded shapefile created in QGIS 2.8.9 with following data type in PostgreSQL as shown in image. After import the shapefile in PostgreSQL the data type shown as below. When I export the data ...
0
votes
0answers
334 views

How to automate an insert in a PostgreSQL database using Python

I'm developing a plugin in qgis that makes a selection, identifies the IDs of the selected elements, and wanted it to insert the selected IDs into the PostgreSQL DB. I have this code to do an insert ...
0
votes
0answers
579 views

Update table with condition from another table in postgresql

I have two table name servers and sites. Servers contains timing and workmode and geometry values. Where sites contains geometry values and an empty time_stamp column. I want to fill this timing ...
0
votes
0answers
119 views

Best performing query st_intersects()

I am running PostgreSQL 9.4 and PostGIS 2.3 For the background: I developped a DB on a regular machine used as host. I needed to use a view+rule system to allow the work to be done on spatial subsets ...
0
votes
0answers
123 views

How to return a polygon geometry from overlapping rows that share a same value in another column

This one is not very easy to explain but basically I have a table with postcodes and a table with area boundaries. These boundaries have an area_id and postcodes that match the postcode table This ...
0
votes
0answers
190 views

How to merge geometries in postgis if they intersect?

Basically I want to merge all the line segments that intersect into one line as shown below in orange So each line segment would have their own id Edit: seems this is too hard to solve with SQL, one ...
0
votes
0answers
202 views

How to set the nodata of Clip raster in postgis to export as tif

I'm trying to crop a raster by polygon and export as TIFF data. The problem is how to set the nodata around the polygon in the raster using psycopg2 in python, because the default value is -3....
0
votes
0answers
309 views

aggregate on geometry is really slow

I have trouble optimizing a query for analytics. Ok so I have bunch of table (lets say, cable, chamber). All this table have a geometry. This geometry can be linestring, point, etc... (all the same ...
0
votes
0answers
260 views

POSTGIS to dissolve all features

I have a multi polygon with spatial field layer with and 3000 records and I want to use dissolve all in 1 record. On DISSOLVE algorithm in QGIS you have a choose to dissolve all features from layer ...
0
votes
0answers
118 views

PgRouting dijkstra returning null cost values

I have been following the tutorial at https://anitagraser.com/2011/02/12/drive-time-isochrones/ in an attempt to create an isochrone map showing drive times to the nearest medical practice in my ...
0
votes
0answers
134 views

Getting the Intersection of lines not at same location

I have got area with line strings that are not quite similar to each other means that at some places they are not exactly at same position. This can be seen in the picture. I am making a buffer on ...
0
votes
0answers
166 views

Display arrows on each segment of a multines feature, using GeoServer and SLD

I'm using the SLD code below to display arrows along lines with GeoServer 2.10: <Rule> <Name>Lines</Name> <Title>Lines</Title> <LineSymbolizer> <...
0
votes
0answers
413 views

How To Use ST_Union in PostgreSql?

I have a spatial table (polygons,polylines) in a database. These are digitized by multiple people. For example, a state is divided into multiple Grids and given to multiple people to digitize using ...
0
votes
0answers
316 views

Importing .gpx/.shp file via ogr2ogr into postgis db

I have a file with lines that make up the european coastline, and I'm trying to import it into my database. This is what I tried: ogr2ogr -f "PostgreSQL" PG:"host=localhost user=postgres password=...
0
votes
0answers
107 views

Updating OpenStreetMap in PostgreSQL?

I can't use imposm tool, because it is not compatible with Nominatim. So it seems that I can use only osm2pgsql. But is there any ready to use toolchain, that allows to update OSM in an easy way. I ...
0
votes
0answers
78 views

Adding QGIS table data to print composer

I'm a bit new to the QGIS world. I have been trying to set up a print composer layout with PyQGIS. I want to generate a map and add a table beneath. I would like to get the source of the data for this ...
0
votes
1answer
191 views

How do I append three columns from a table to my active table?

I am trying to append three columns from srs2016 to srs2013_1 using this sql. INSERT INTO srs2013_1 (the_geom, agency_responsible, description) SELECT the_geom, agency_responsible, ...
0
votes
0answers
81 views

Simplify multidimensional list of points into track/linestring

I have a bunch of points representing the movement of a target: Track_123 = [Point_1, Point_2, Point_3 ... Point_N] Where each point is a multidimensional vector: Point_N = [Lat, Lon, Time, Speed, ...
0
votes
1answer
191 views

Lizmap editor won't save edits to pgsql

So I have successfully installed Lizmap, Qgis-server, Apache, and a BigSQL postgreSQL database and published a map with a working WFS pointed at a PostGIS enabled table in my database. When I try to ...
0
votes
0answers
195 views

Can GDAL be used to load raster data into Postgres/Postgis geodatabase?

I would like to know if GDAL can be used to load raster data to a postgres/postgis geodatabase I had read that there was problems with the GDAL postgis raster driver? I was wondering what particular ...
0
votes
0answers
54 views

Can a MapServer datasource be a materialized view?

I'd like to find out if there is a way that a datasource in MapServer can be a materialized view, this is due to the presence of indexes, these cannot be in views but only in materialized view - ...
0
votes
0answers
98 views

Trouble shooting ogr2ogr PostGres conversion to shapefile

I have a postgres database that I am trying to do a bulk export of to individual shapefiles. The database consists of several hundred tables. If I run this command: ogr2ogr -f "ESRI Shapefile" ...
0
votes
0answers
385 views

ST_Equals not matching with exact geometry

I followed many question posted on the same subject but unable to solve my problem. I have same kind of tables in 2 different database (tables with linestring geometry), I need to update id from one ...
0
votes
0answers
185 views

import SpatialPolygonsDataFrame (SPDF) into PostgreSQL DB schema from R

This is a followup question to Writeogr to PostgreSQL/PostGIS database with R How can I choose the schema where to put the SPDF (or any other spatial object)? postgis_insert(con, df=catchments, tbl=...
0
votes
0answers
102 views

Limitations of PostGIS Shapefile Import GUI tool?

I would like to improve my knowledge regrading this issue. I am using PostgreSQL version 9.5 (x64bit) on Windows 7 Enterprise (x64bit Machine). I have a table containing 3515214 rows of X,Y ...
0
votes
2answers
206 views

Odbc_fdw.control is missing

I am using cartoDB and Postgres 9.3 including PostGIS. When I try to create development user in cartoDB I get this error: 'CREATE EXTENSION IF NOT EXISTS odbc_fdw SCHEMA public;' ERROR: could not ...
0
votes
0answers
163 views

Creating a table and inserting selected streets using plpgsql functions

I am trying to select all streets within 50 meters distance from each address location and store them in a separate table using Postgresql (plpgsql) functions. So far, I have written the following two ...
0
votes
0answers
280 views

Database modeling for big data (big set of points X big sets of possible values for each points)?

I'm building a PostgreSQL database with the intent to host modelisation results for different projects. Each project has a set of receptors points (~5000-10000 receptors) and have many results sets (...
0
votes
0answers
149 views

Is it possible to use pgsql2shp utility in PL/pgsql environment?

I intend to use PL/pgSQL to generate calculation points on streets with respect to address locations. I am aware that using pgsql2shp utility, a shapefile can be generate using postgis query. I was ...
0
votes
0answers
66 views

PostGIS - raster coodinates - no result for negative values of GPS

I have data in raster and try to run this query WITH ref As ( SELECT (ST_SetSRID(ST_Point(-20.12, 51.50), 4326)) AS pt ) SELECT ST_Value(raw_data, 1, ref.pt) AS val_0 FROM model_data The problem ...
0
votes
0answers
425 views

Unable to install pgrouting for postgres 9.3.5/postgis 2.1.7

I am running postgres 9.3.5 and postgis 2.1.7 on Ubuntu Server 14.10. I am trying to install pgrouting, but having no luck. 1) I tried the following based in the official workshop documentation: ...
0
votes
0answers
308 views

postgis ST_Intersect on 2 valid geometries throws TopologyException

Using Postgis 2.1 on PostgreSQL 9.3.4, I'm getting an error when I try to intersect two valid geometries: TopologyException: found non-noded intersection... These geometries are created on the fly ...
0
votes
0answers
206 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 ...
0
votes
0answers
264 views

PostGIS: Lines from location touches but not intersecting polygons

I have OSM .shp file of buildings. I created vertices along each line of the building in that way: vertices (red) & mid points (green). The mid points made by ST_Line_Interpolate_Point() after ...
0
votes
0answers
1k views

Problem with ST_IsValid: Unknown geometry type: 9 - CompoundCurve

The following is an example of a gml:Curve that was loaded into Postgres using FME. <gml:Curve srsName="urn:ogc:def:crs:EPSG::27700" gml:id="os.vmd.ny.38124.geom"> <gml:segments> <gml:...
0
votes
0answers
61 views

Use of constraints and Spatial indexes in Postgis

We'are using Postgis to manage our geo data. They are coming from several sources, like Open street map, VPF, Shape and so on. We have defined constraints on the tables, and spatial indexes on ...
0
votes
0answers
369 views

MultiPatch Shapefile import into PostGIS Database

I have geometry of buildings in Shapefile MultiPatch format. I want to import it into PostGIS database in the same format. Is that even possible? If yes, then how?
0
votes
0answers
39 views

Display graduated QGIS buggy?

I counted points in polygons in PostGIS like this UPDATE hexagons SET count = (SELECT count(*) FROM points WHERE ST_Within(points.geom, hexagons.geom)); That came back with that UPDATE 1554 That ...
0
votes
0answers
460 views

How to get elevation of a coordinate given a dtm file out of a PostGIS?

What would be the best way to get elevation of a coordinate out of a PostGIS database (SQL/plpgsql) given a GeoTIFF dtm file? Importing as PostGIS Raster (which seems more costly) and calling ...
0
votes
0answers
55 views

Query processing time for network table creation using pgRouting 1.05

I am trying to follow these steps for creation of a network table using pgRouting 1.05. In my case I have two road geometry tables, one is bangalore_road and the other is all_india_roads. Obviously ...
0
votes
0answers
237 views

Create table into PostgreSql from Geoserver SQL View query

I know how to connect Geoserver to PostgreSql and create SQL View queries with parameters from Geoserver, but ;it is possible to create a new table into PostgreSql from a Geoserver Sql View with ...
0
votes
0answers
67 views

How to create unique plane with PostGIS?

How to create a unique plane, with (x , y ,z) as center and a vector as normal, in PostGIS? I already saw "Make3DBox" function, but it does not make a unique one.
0
votes
0answers
138 views

Routes through centroids and array functions

I have a table stopcentroids4 like in figure with the following fields: gid, routes, geom and i want to draw a line for each route that runs through centroids. In my code there is a mistake. ...