Questions tagged [postgresql]

PostgreSQL is an open source object-relational database system.

Filter by
Sorted by
Tagged with
30
votes
3answers
10k views

Which function for creating a POINT in PostGIS?

When defining a Point in PostGIS, when do you decide to use which of the following? ST_SetSRID(ST_MakePoint(lon,lat),4326) ST_SetSRID(ST_Point(long,lat),4326) ST_SetSRID(ST_GeomFromText('POINT(lon ...
35
votes
0answers
18k views

How to use PostgreSQL/PostGIS with ArcGIS for Desktop? [duplicate]

Has anyone used PostgreSQL with PostGIS successfully? I am a semi-experienced ArcGIS user and want to build out a database and connect it to ArcCatalog. I've scoured the internet for resources but ...
31
votes
4answers
18k views

SQL query to have a complete geojson feature from PostGIS?

I would like to get a geojson feature with properties from PostGIS. I have found an example to have a feature collection but I can't make it works for just a feature. SELECT row_to_json(fc) FROM ( ...
14
votes
10answers
16k views

Broken packages and unmet dependency installation QGIS, PostgreSQL and postgis Ubuntu 14.04

I am aware that this could be a duplicate topic but I am struggling for a several days. I upgraded QGIS from 2.8 to 2.18 and after that everything get broken. I couldn't use PostGIS extension on ...
10
votes
5answers
4k views

Split lines into non-overlapping subsets based on points

Given a table with line geometry, and one or more points that are snapped to this line in a separate table, I would like to split each line with one or more intersecting points at each of the ...
24
votes
2answers
15k views

In PostGIS is it possible to create a View with a unique ID?

When I create a view in PostGIS is there any way to add a unique ID to that view? Just like the "gid" field in any other PostGIS table? Edit: Sorry I should have included this in the original post. I ...
15
votes
1answer
5k views

How to properly set up indexes for PostGIS distance queries?

I'm building an application that is supposed to query and return every Record in a table that is X kilometers away from PointX. Records and PointX's positions are determined from (long/lat) ...
6
votes
3answers
1k views

Numbering polygons according to their spatial relationships?

I have, for example, this layer in postgresql (polygons): table : poly (objectid: integer, num: integer, shape: geometry) I want to enumerate the polygons from left to right and top to bottom like ...
9
votes
1answer
4k views

Using PostgreSQL with QGIS and ArcGIS?

Is it possible to use PostgreSQL with QGIS and ArcGIS? i.e. one database for different clients. Is it anything that I have worry or any problems to use the DBMS from two different software? I know ...
6
votes
5answers
5k views

PostGIS doesn't use spatial index with ST_Intersects

I imported the data from openstreetmap using osm2pgsql and copied it another table structure. If I made a query for containing rows using st_intersects, st_contain or someone else, postgis don't use ...
6
votes
2answers
10k views

What does 'DBMS table not found' message mean?

I have a plain postgressql table (version 8.2). I am trying to connect to it to it from ArcGIS desktop 10.0, and use as a query layer, as given in this document: http://help.arcgis.com/en/...
3
votes
3answers
6k views

How to enable SFCGAL in PostGIS?

I have the problem with the enabling extension SFCGAL. CREATE EXTENSION postgis_sfcgal; doesn't work for me even I have the PostGIS v2.2. When I do that, it throws an error ERROR: could not ...
29
votes
2answers
44k views

Adding shapefiles to PostGIS database

I was able to create a Postgresql database and then I executed, createlang plpgsql geospecies psql -d geospecies -f postgis.sql psql -d geospecies -f spatial_ref_sys.sql So now can I directly import ...
49
votes
3answers
10k views

What is the purpose of PostGIS on PostgreSQL?

PostgreSQL already supports spatial data types, operators and indexing. What does PostGIS provide exactly that made it necessary to exist as an extension to PostgreSQL? Why don't we all just use ...
17
votes
3answers
8k views

Identifying road intersections using PostGIS

I'm trying to identify where roads intersect each other, and to make a point at this intersection, with the number of roads that form the intersection listed. I was wondering if there was some way ...
13
votes
4answers
11k views

Percentage of polygon in one shapefile within polygon of another

I'm a newbie, apologies if this is obvious/has already been asked and answered but I couldn't find anything. I have two shapefiles: 1. an administrative boundary layer for a county in the UK known as ...
11
votes
1answer
835 views

Batch load multiple shapefiles to Postgis

According to the shp2pgsql help, the options drop, append, create and prepare are mutually exclusive. So, if I want to create a table from a shape and then append multiple other shapefiles, I do ...
20
votes
3answers
15k views

Shapefile loader in pgAdmin 4

Recently I have updated my postgresql version. I have also upgraded pgAdmin from 3 to 4. But I was unable to load shapefile. I didn't find any plugin called shapefile loader. How can I get the ...
8
votes
1answer
8k views

How to import a raster into PostGIS?

I'm trying to follow the instructions here to load a raster into a PostGIS database: python raster2pgsql.py -s 4269 -I -r *.tif -F myschema.demelevation -o elev.sql I understand that I replace * ...
6
votes
2answers
4k views

GDAL Python bindings not picking up OGR PostgreSQL driver

I have a windows server (Microsoft Windows Server 2003) with: Python 2.7, PostgreSQL 9.04-1, GDAL 1.8.1 (installed from the windows installer (gdal-18-1500-core.msi) hosted at http://vbkto.dyndns....
13
votes
3answers
4k views

PostgreSQL trouble editing points / lines / polygons in QGIS

I have a problem with postgresql and QGIS : I can add points/lines/polygons and fillup field But I can't : delete points/lines/polygons modify points/lines/polygons (move or modify field) ex : ...
4
votes
2answers
11k views

How to find nearest line from point using PostGIS?

I have a table of Roads in PostGIS which geometry is "geometry(MultiLineString,4326)". Its attribute are gid osm_id name ref type oneway bridge maxspeed geom Now i want to know the ...
6
votes
3answers
6k views

WFS-T Edit using OpenLayers, Geoserver, PostgreSQL (“error performing insert”)

I tried following the tutorials: http://www.gistutor.com/openlayers/22-advanced-openlayers-tutorials/47-openlayers-wfs-t-using-a-geoserver-hosted-postgis-layer.html and: http://apps.who.int/tools/...
3
votes
1answer
2k views

Get a single cluster from cloud of points with specified maximum diameter in postgis

I have on the order of 2M Geometry Collections with N (median: 2, stddev: 6.6, 75th: 6, 95th: 21) Point objects (repeated points allowed and important) in a PostGIS table. I want to identify clusters ...
21
votes
2answers
5k views

Publishing PostGIS Rasters in GeoServer?

How do I publish PostGIS rasters using GeoServer? I have spent a lot of time trying to create a Raster datasource using Image Mosaic JDBC but with no luck. Steps performed: 1. Downloaded and ...
13
votes
2answers
8k views

Setting up PostGIS database and opening it in QGIS on Windows?

I have tried to follow the manuals for setting up a PostGIS database to be accessed by users with QGIS, but at the moment I cannot understand enough to progress? My situation; I am a trainee ...
13
votes
3answers
10k views

calculating percent area of intersection in where clause

I have a table of polygons (census block groups) in postgres. I want to tag each block group with the town (another polygon table) it mainly resides within. Is this possible? I'm thinking I'd need to ...
12
votes
1answer
11k 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=...
8
votes
1answer
3k views

How to create linestrings with a definite angle and length, that are fixed to a point

I want to create linestrings with a definite angle (e.g. 160°) and length (e.g. 2m) that are fixed to a number of points of another linestring. So, I want to use the ST_DumpPoints function to find the ...
15
votes
2answers
14k views

Finding the closest Geometry in PostGIS

I've been looking over the PostGIS "API" of functions, and I notice that most of them take two elements to compare. For example, the ST_Distance function takes two geometry/geography elements to find ...
9
votes
4answers
9k views

How to fix performance problem in PostGIS ST_Intersects?

I'm a newbie in postgis and I have a problem in query performance. This my query: SELECT DISTINCT ON (userid) userid ,ST_AsText(position), timestamp FROM table1 WHERE ST_Intersects ( ...
4
votes
3answers
1k views

PostGIS and ArcSDE mixed environment

I am trying to setup up a enterprise geodatabase with ArcGIS 10.4.1 and PostgreSQL 9.4 where I can use PostGIS for my SQL queries in PostgreSQL and have the sde functionality. I have a windows ...
3
votes
2answers
7k views

How to install PostgreSQL 9.2 with PostGIS 2.0 on Ubuntu 11.10 (or higher)?

I have read this question: https://dba.stackexchange.com/questions/25033/install-postgis-v2-for-postgresql-v9-2, but it didn't resolve my problem. I added the PostgreSQL backports PPA and installed ...
2
votes
2answers
5k views

Take from multilinestring the start and end points

I have to use the A* algorithm and my geometry is in multilinestring and I want to take stratpoint(x1,y1) and endpoint(x2,y2) . I use this queries but the columns after the execute is still empty ...
13
votes
1answer
4k views

How to perform a spatial join of point and polygon layers in PostGIS?

I have a point dataset representing households that I want to associate with a parcel layer (i.e. assign its parcel_id). With ArcMap, I could spatially join the polygons to the points and specify that ...
7
votes
1answer
2k views

Deleting small gaps (slivers) between polygons?

I try to delete the slivers that appeared when I have merged some polygons. I have two different cases: For the CASE 1, no problem I fill the gap with: CREATE OR REPLACE FUNCTION sliver_killer(...
4
votes
1answer
1k views

How to properly establish a PostgreSQL connection using QgsCredentials

I'm trying to establish a Postgres connection using QgsCredentials as described here: How to access user credentials in database connection? The only problem is that I cannot establish a working ...
3
votes
1answer
2k views

How to create dissolved buffers in QGIS with PostgreSQL/PostGIS?

Creating a dynamic buffer in QGIS I use the following query: CREATE VIEW buffer40units AS SELECT gid, st_buffer(geom,40)::geometry(polygon, 31492) AS geom FROM point; Now I want to dissolve ...
2
votes
2answers
902 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....
6
votes
2answers
1k views

Accessing non-spatial PostgreSQL tables using OGR Python bindings?

When connecting to PostgreSQL/PostGIS using the OGR Python bindings is it possible to get non-spatial table names (i.e. OGR layers) from the connection? Currently, doing: conn = ogr.Open("PG: #...
5
votes
3answers
1k views

What does the values in column clazz (osm2po) mean?

I have a given table in postgreSQL with OSM data which is used for routing purposes in Java with PGRouting. There exist two columns which I don't understand. They belong to osm2po. They are labeled ...
5
votes
1answer
660 views

ArcMap error drawing postgis layer - The number of points is less than required for feature

I have one big polygon called huc and a multilinestring called streams, I am running an intersection on between the two layers to clip the streams to the huc. here is what I have so far drop table ...
1
vote
1answer
52 views

Get barrier edge id

I have a table of POINTs that represent barriers. I would like to be able to find the closest edge id on the edge graph so that I can have the routing algorithm ignore that edge (thus route around it)....
36
votes
5answers
35k views

Why does PostGIS installation not create a template_postgis?

I went this route for installation of PostgreSQL and PostGIS: PostgreSQL using the standalone KyngChaos, PostGIS install from KyngChaos as well... pgAdmin3 from PostgreSQL Tools Now when I launch ...
7
votes
2answers
10k views

How to perform SQL queries and get results from QGIS python console?

I'm writing a python script using QGIS API. I have to get results from postgres database table. The table has geometry field and other non-geometry fields. I want to perform SQL queries on non-...
12
votes
4answers
11k views

Connecting to ArcGIS Desktop PostgreSQL database (running locally)

I know this is a total noob question but then again, when it comes to PostgreSQL I am a total noob... I have installed the OpenGeo suite on my computer, running Windows 7, 64-bit. I have ...
11
votes
2answers
3k views

How do you display related records in QGIS?

Coming from an esri background I have always used relationship classes in a gdb to display records related to a feature. Eg. Relate 1 survey point to many documents, photos etc. Does any one know how ...
7
votes
2answers
15k views

How to connect to Postgres with QGIS?

Oddly, it should be simple, but I can't connect to a local PostGIS database. My installation is with Mac OS X (10.8), Postgres.app (in doc it's said to have PostGIS included) and Quantum GIS 1.8. ...
34
votes
2answers
32k views

How do I check which version of GDAL I have and how many?

I have installed in my computer the following software: PosgresSQL 9.3.2.3 PostGIS 2.1.1.1 QGIS 2.0.1 Dufour (this one installed through the OSGeo4w package) QGIS 1.7.0 Wroclaw As far as I know all ...
22
votes
4answers
6k views

How can I optimize pgrouting for speed?

I am using pgrouting on a postgis database created through osm2pgrouting. It performs very good on a limited dataset (3.5k ways, all shortest path A* searches < 20 ms). However since I have ...