PostgreSQL is an open source object-relational database system.

learn more… | top users | synonyms (1)

5
votes
1answer
112 views

How to load layers from postgres using SELECT statement?

I am trying to load a postgres table (columns extracted using a select statement) from the python console in QGis: The following works fine and the layer loads as expected: uri = QgsDataSourceURI() ...
2
votes
1answer
55 views

postGIS - Error: couldn't load library C:/Program Files/PostgreSQL/9.5/lib/postgis-2.2.dll: %1 is not a valid Win32 application

Environment - Windows10(x64), PostgreSQL 9.5(x64) I installed GDAL, PROJ.4 and GEOS (via OSGeo4W ) Then I installed postGIS via Stack App Builder (I have tried both versions - x32 and x64). In both ...
1
vote
1answer
117 views

Finding all polygons inside of a polygon

How to find all the polygons inside of a polygon? I tried ST_Intersects and ST_Contains but they are booleans variables. I have a list of polygons in WKT format, so I am trying to pull all the ...
4
votes
1answer
59 views

PostGIS Intersection weird behavior

I can observe a strange behavior with my PostGIS query. I am trying to accomplish the following: I have one polygon layer I have 2 different line layers layer with horizontal lines layer with 45° ...
4
votes
3answers
121 views

Translate PostgreSQL column names in QGIS

I have connected to PostgreSQL and imported the layer to QGIS. The column names are written using English letters. Can the names of columns be automatically translated to another language?
1
vote
0answers
28 views

PostGIS Nearest Neighbor query performance - can it be quicker?

I want to canvas the community to see if my nearest neighbor query can be optimized at all. I am happy with my current results, but I'm always up for improving. I have 2 tables, lakes and towns. I ...
0
votes
0answers
33 views

Postgis Database: How can I get all gps points between specified timestamps and specified region?

I am totally new to DBMS. I have traces of vehicles in different csv files for each user. Format: name,timestamp,latitude,longitude. 1) How to implement RANGE Query which asks for all the gps points ...
1
vote
1answer
45 views

Why missing address IDs in postgresql/postgis query results?

I have two tables "ad" and "st" in my postgres DB. Table ad has 87 rows while st has 16,060 rows. Following code works perfectly to select st.geom (streets' geometry) within 50 meter radius with ...
2
votes
0answers
26 views

Downloadable pdf format of GRASSGIS 7.0 manual [closed]

Can anyone help with a link or something that could enable the download of a pdf version of the grassgis manual? I am having a hard time trying to convert the html version into printable pdf. Nkeze
2
votes
1answer
2k views

How to install PostGIS and pgrouting on Windows if Stack Builder fails to connect?

I know this has probably been covered and that there are plenty of guides out there, but for the life of me I cannot get pgrouting installed. All guides/how-tos are different or assume prior knowledge....
0
votes
1answer
13 views

How to extract seamarks from OSM PBF?

Ref to this: http://forum.openseamap.org/viewtopic.php?id=2060 and this: How to extract primary and secondary roads from OSM data? and this: http://wiki.openstreetmap.org/wiki/Seamarks/Seamark_Objects ...
0
votes
1answer
44 views

How to create a line into a table in Postgis?

I need to draw a line and create a new entry on an existing table in Postgis. Say I have a table called myTable and two points with coordinates 528115,181037 and 528115,181037 using the EPSG:27700 (...
0
votes
0answers
22 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
20 views

Error connecting to PostgreSql 9.2 using Autocad Map 2011

I want to connect Postgresql 9.2 database (installed at localhost , port-5432) from AutoCad Map 2011 (installed in same PC). I have successfully copied all required dll files (total 13 nos.) from ...
0
votes
1answer
117 views

Parse and load GPX into database? [closed]

Having a PostgreSQL database, with the following tables: CREATE EXTENSION postgis; DROP TABLE IF EXISTS trips CASCADE; DROP TABLE IF EXISTS locations CASCADE; CREATE TABLE IF NOT EXISTS trips ( ...
2
votes
1answer
40 views

Problems with 'layer exists ' while running ogr2ogr

I run command: ogr2ogr -f PostgreSQL PG:"dbname=regional host=localhost user=*** password=***" Hsp_09_13.MIF -t_srs EPSG:3857 -nlt MULTIPOLYGON -overwrite -nln postcode_sector_boundaries And I have ...
0
votes
0answers
49 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 ...
1
vote
1answer
71 views

Convert 5-digit string number into a readable date in CartoDB

Based on a couple of assumptions here 1) I have about 6 clean formatted date columns imported from Excel that CartoDB converted into 5-digit strings. I cannot select 'date' in the column label. 2) ...
0
votes
1answer
49 views

How to instal Pg-Trgm on Windows?

I am currently working on a computer with windows 7 on it and i need to instal the Pg-Trgm module for postgresql 9-5. I have to admit that i am kind of lost here since i don't have any idea how to do ...
1
vote
1answer
111 views

How to add X/Y coordinates and count of overlapping points in a postgresql table?

How to add to my postgres/postgis table the following columns X coordinate | Y coordinate | count (number of points in same location) from an existing table populated with geometries type POINT
0
votes
0answers
50 views

ST_Distance between ST_Centroid and ST_Y

I'm trying to make a ST_Distance between a ST_Centroid and a ST_Y... But ST_Centroid is a geometry and ST_Y is a float. How can I make this? My query: SELECT ST_Distance(ST_Centroid(ST_Collect(...
4
votes
1answer
127 views

Import a shapefile to postgis with ogr2ogr gives: Unable to open datasource

I want to use ogr2ogr in order to import a shapefile in a postgis database. I have successfully installed ogr2ogr and I run from the pgsql the following command: ogr2ogr -f "PostgreSQL" PG:"host=...
1
vote
1answer
67 views

In Postgres how to create columns with geometry?

I have point data stored in the postgis server and I need to create a new table with their geometry values. How can I extract their coordinates and translate them into a table. Something like: ...
9
votes
0answers
270 views

How to extract sublinestring from a linestring with overlapping segments

Problem statement: Given a bus route (defined as a linestring ln.the_geom), and a set of bus stop locations (pt1.the_geom,pt2.the_geom,...ptn.the_geom), divide the bus route into segments between bus ...
3
votes
1answer
86 views

PostGIS crosstab on multiple point tables

I have several point tabels, lets say 3. Tables are alike with two columns: a integer value and a geometry like: table sand1 (value int, geom geometry) table sand2 (value int, geom geometry) table ...
1
vote
1answer
65 views

Loading GML data into Postgres with ogr2ogr

I'm working with a new OS dataset, Highways Network, trying to get it loaded into Postgres and set up in some kind of usable state. I've loaded all the data already, but it seems some elements of ...
0
votes
1answer
43 views

Writeogr to PostgreSQL/PostGIS database with R

I'm producing some Spatialpolygondataframes with R that I would like to upload to my PostGIS Database. I've found out that the way to do this is via the writeOGR() function using the PostgreSQL driver....
1
vote
1answer
49 views

Where to use the shp2pgsql commands?

I need to upload some very large tables (over 10Gb) into Postgis so I actually found this link What is the best hack for importing large datasets into PostGIS? The DB manager is far too slow and only ...
1
vote
0answers
32 views

QGIS not loading default layer style from PostgreSQL database on startup

I have saved a vector layer style to my PostgreSQL database source. I then amended the layer_styles table record for the relevant layer by editing the styleqml field (just changed the outline_width ...
0
votes
0answers
32 views

run shp2pgsql command in c# application

I worked for a month on this command but in vain ; really if someone already tried to help me find the error , because when I load the application displays the message to me as the shapefile was ...
0
votes
1answer
35 views

How to edit a table in order to count the number of points in the same location?

Using QGIS, I have a point shapefile that has multiple points with same coordinates across different locations. I need a count of those points. For example there are 15 features that have the exact ...
1
vote
2answers
37 views

QGIS error in merging large point shapefiles

While trying to merge 2 large point shapefiles using the merge shapefiles tool under the data management. These point files have over 20Gb and have the same coordinate system. Problem is, that the ...
1
vote
2answers
52 views

Show results of ST_Union in QGIS

Currently, I am working on SQL in PostgreSQL and I execute query of vector data by "ST_Union" function for the spatial data. Now, I want to access the result of query in QGIS 2.14, but I can't. Is ...
2
votes
0answers
28 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 = "...
1
vote
1answer
58 views

Creating shapes/lines from geo coordinates in postgis

Following the suggestion from this topic I'm posting my question here. I have a table in postgres/postgis with geo data and I want to show them on map via qgis. Here is the table: city | street ...
0
votes
3answers
125 views

How can i use WFS in Openlayers 3 with mapserver and PostGis?

I didn't find a documentation to display features ( vectors ) combining MapServer ( mapfile ) and PostgreSQL/PostGIS and OpenLayers 3 ?
1
vote
0answers
21 views

I am using an example fracnsites to make a similar map but I don't under stand the ajax code?

I am using an example fracnsites to make a similar map but I don't under stand the ajax code? The example has js code: var map, fields = ["gid", "createdby", "featname", "feattype", "status", "acres"...
2
votes
2answers
146 views

Load style from database in QGIS

Maybe it's a stupid question, but I don't know how to connect layer style with database in QGIS. When I save the style to postgres database in QGIS, it automatically saves the style in the table ...
0
votes
1answer
45 views

ST_Union or ST_Collect on PostgreSQL union result?

Newbie to PostGIS/Postgres. I have the follow query which returns me 2 GEOMETRYCOLLECTIONS. Is there a way within my current query to return one polygon? SELECT ST_AsText(ST_Collect(ST_Simplify(geom,....
0
votes
1answer
55 views

Geocoding addresses based on own reference data

I have a historical addresses dataset in form: |Name |Description | Where "Name" is a single field with house number, street name, city and country. I have digitised two georeferenced ...
1
vote
2answers
104 views

Importing OSM data into PostGIS and display into QGIS

I am new to all things GIS! However, I need to set up a full GIS infrastructure for a project. I have installed a Postgres server with PostGIS. That is on ancient hardware for now, 32bits processor ...
1
vote
1answer
56 views

Lines from points in postgresql postgis

I have this table (all row aren't in order): time, id, latitude, longitude time is character varying and the format is: dd/mm/year hh:mm id, latitude and longitude are numeric I would create lines ...
2
votes
1answer
86 views

Displaying data from a partitioned PostgreSQL / PostGIS table is unacceptably slow in QGIS

I have two very similar datasets of 27 million polygons stored in PostgreSQL / PostGIS. One is loaded as a single table, with spatial indexes and regular indexes on key fields. The other is ...
1
vote
1answer
43 views

PostGIS with clause not using spatial index

This query uses the spatial index and it's quite fast: explain select a.the_geom from chicago_major_streets a, stormevents_locations_2014 b where st_intersects(a.the_geom,b....
0
votes
1answer
50 views

select neighbour polygons in sql

I have a database of few milion records (all houses in my country). Now I want to select all records who have a neighbouring record (a house next to it) and give this a status 'T'. Each record has a ...
5
votes
5answers
632 views

Improve performances on distance coverage routing - PostgreSQL

I have to create layers of land coverage from one point to anywhere around on a given street network for a given distance (ex: FROM house, DISTANCE 30000m) An important factor to understand; I need ...
0
votes
2answers
47 views

Not able to create postgres Sql file using Osm2po

I am newbie to the GIS community. After downloading the osm2po jar file, I have run following the instructions from underdark tutorial. Also, from Carsten's answer in gis stack exchange, I have ...
15
votes
1answer
1k views

Can PostGIS and QGIS provide dependable multi-user editing?

After considering a few options, I'm trying to give my 15-20 QGIS 2.2 users the ability to concurrently view/edit one or two basic point PostGIS layers, largest one has just 16,000 records. I've ...
0
votes
0answers
23 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 ...
6
votes
1answer
115 views

Leveraging capabilities of PostGIS for large (ish) tables in PostgreSQL?

I am using PostgreSQL and PostGIS to do some exploratory analysis and eventually spatial analysis. I have a table with 40M+ points (geolocated tweets) in the United States that has a spatial index. I ...