PostgreSQL is an open source object-relational database system.

learn more… | top users | synonyms (1)

1
vote
0answers
27 views

How can I style a feature that has multiple values for the same field?

I'm opening a PostgreSQL Table on QGIS which has multiple rows related to the same object (think of how you'd register distinct apartments on the same building). I do get to style it based on any of ...
1
vote
2answers
42 views

Select transposed values

I am trying to do a conflation. This is my method, and my problem: METHOD Step 1: I have two tables with road geometries. I am using ST_Dwithin to subselect geometries from table 'A' that lie within ...
1
vote
1answer
34 views

Find row with a polygon containing a specific lat/long

I have a Postgres table that has two columns – address, and geom (which is a multipolygon without any SRID set, but it should be 2263). I'm trying to find all of the rows that contain a given point, ...
2
votes
0answers
36 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 ...
0
votes
0answers
8 views

how to fetch fields having difference in values from two tables in postgresql

I have two tables with same fields.I want to return fields whose values doesn't match with other table field values. tableA field1 | field2 | field3 | 1 | 2 | 3 | ...
1
vote
1answer
73 views

Select points based on value and proximity

I have a set of points in a table, each has a value between 1 and 100. How could I reduce this to the point with the highest value within a radius distance X. So that no point is ever closer than X ...
1
vote
0answers
37 views

Cannot store raster in custom database schema

I want to store an elevation raster in a postgis database. However a problem occurs. I use the following command: raster2pgsql -I -C -e -Y -F -s 4326 -t auto dem.tif schemaname.filename | psql -U ...
2
votes
1answer
52 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
58 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° ...
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
3
votes
1answer
38 views

QgsDataSourceuRI and left outer join

I'm trying to load data from different tables using left outer joins into a QgsVectorLayer. The approach I'm following so far is the one from the developers cookbook here. The underlying database is ...
0
votes
1answer
42 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
21 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
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 ...
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
19 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 ...
1
vote
3answers
48 views

How to load the only selected features from the layer in postgresql to Qgis canvas

I am using the uri to load the layer from the postgres to qgis canvas but it is not loading the selected features without where condition it is loading the lAYER. canvas = qgis.utils.iface....
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 ...
0
votes
0answers
49 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(...
1
vote
1answer
69 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) ...
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: ...
0
votes
1answer
45 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 ...
0
votes
1answer
115 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 ( ...
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
4
votes
1answer
121 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=...
0
votes
1answer
42 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....
3
votes
1answer
84 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 ...
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 ...
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 ...
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 ...
2
votes
0answers
27 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 ...
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 ...
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"...
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
1answer
43 views

Problem with loop for pgr_Dijkstra

I try to use pgr_Dijkstra through Postgis and Pgadmin to find the shortest path in a network between a lot of nodes. So I thought I could to "automate" the process with a PLSQL query. Here is a query ...
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
54 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
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 ...
0
votes
1answer
49 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 ...
2
votes
1answer
85 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
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 ...
1
vote
1answer
61 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
0answers
19 views

Maximum likelihood Between 2 linestrings in Postgre?

I created a lot of linestring datasets and loaded them all in PostgreSQL, I want to compare said linestrings 2 by 2 using a maximum likelihood Query which would give me a coefficient proportional to ...