Episode #125 of the Stack Overflow podcast is here. We talk Tilde Club and mechanical keyboards. Listen now

Questions tagged [postgresql]

PostgreSQL is an open source object-relational database system.

Filter by
Sorted by
Tagged with
6
votes
4answers
811 views

How to split circles in 12 sections in PostGIS?

I have a point layer in my PostgreSQL db and I would like to split a large number of circles into 12 sections each. Sample scenario is: The circle corresponds to a 50 meter buffer of a point layer, ...
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
2k views

Postgres Log Error

I am using Geoserver + GeoWebCache + Postgres for an application. On the application side there is no bugs but on postgres I am getting this log. Please explain and help me to solve this 2014-03-14 ...
6
votes
3answers
2k views

What's wrong with my ArcGIS - PostGIS setup?

I have installed ArcGIS and PostGIS, and I'm having a hard time connecting them. I've imported some shapefiles into PostGIS and I can see them and manipulate them in pgAdmin. The trouble is that I ...
6
votes
2answers
12k views

PostGIS ST within does not exist

I'm quite new to postgresql and postgis so this error will probably due to some rookie mistake. I want to run the spatial query ST_within. When I describe the query like this SELECT nw.entity_id AS ...
6
votes
1answer
2k views

Moving SpatiaLite Table into PostgreSQL using ogr2ogr?

How can I move a Spatialite table from a Spatialite db to a PostgreSQL db using ogr2ogr?
6
votes
5answers
9k views

Postgis shapefile loader 2.1 error: dbf file can not be opened

The .shp, .dbf and the rest of files are fine, I open them in QGIS, AGOL with no problem. In fact, I've tried 3 different layers, and get always the same error. Anyone can please help, as I really ...
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 ...
6
votes
1answer
1k views

QGIS cannot view raster loaded to PostGIS through raster2pgsql

I tried to load a raster GeoTIFF image to a PostGIS database using "raster2pgsql" following the OpenGeo Suite Manual. The command I used is: C:\>raster2pgsql -s 4283 -I -C -M "C:\Program Files\...
6
votes
3answers
3k views

PostGIS : Does the <-> operator work for Geography?

With the recent PostgreSQL/PostGIS version, you can now use the <-> operator to find the nearest rows to a point: Indexed Nearest Neighbour Search in PostGIS According to my tests, this query:...
6
votes
2answers
3k views

How to connect a PyQGIS plugin with Postgres?

I have some questions for you: I created a python-plugin in QGIS, then I created a Qt gui fom my plugin and at last I created a Postgres DB now I need to make a connection between Qt-gui and ...
6
votes
3answers
3k views

Adding a PostgreSQL database to a web map

I am interested in learning how to add a PostgreSQL database to a web map so I can do functions like select by attributes or locations. I am using Leaflet API to make the web map. What are the ...
6
votes
2answers
30k views

Making simple loop in PostgreSQL?

In the PostGIS SQL Editor, I just want to make a loop to make this function work This query is to give the UTM Zone number to one column of Global DTM raster table For Loopid = 0 to 1000, then ...
6
votes
2answers
5k views

ST_Intersects throws an error on mixed SRIDs that && permits [closed]

I found that there is a difference in result between using A && B operator or one of the geo functions like ST_Intersects(A,B) While the ST_Intersects functions return query results without ...
6
votes
1answer
12k views

.NET Entity Framework with PostGIS?

Is it possible to use the .NET Entity Framework with PostGIS? I've reviewed a few options, including hookup it up with Npgsql using http://npgsql.com/index.php/2009/08/how-to-set-up-entity-framework-...
6
votes
2answers
579 views

Get the 2 points immediately before and after a given point in PostGIS

In the picture below: The given point is C, I would like to select A and B, but not Z (which is closer to C than B but it's not immediately after in the LineString). Note that C is always ON the line ...
6
votes
1answer
727 views

Efficiently querying a region-partitioned PostGIS table

I have two PostGIS tables: one of streets, and one of regions, which, apart from spanning the same spatial extent, are a priori unrelated. The streets queries I perform can be restricted to a given ...
6
votes
4answers
3k views

Convert KML to shapefile without losing attribute data

So I want to convert KML files to SHP shapefiles to load it onto PostGIS. Currently I'm using ogr2ogr to do the conversion for me, but it wipes out all the attribute data. How do i preserve this ...
6
votes
3answers
2k views

Qgis is slow with complex 'postgis query' layer. How to speed up?

I have a postgis layer in Qgis that is a result of a complex query. Every screen update, Qgis runs the Query again and that's slow. Any suggestion on how to speed up? EDIT - Here is the query: ...
6
votes
3answers
5k views

Unable find driver PostgreSQL in ogr2ogr

I need use ogr2ogr tool for import osm into PostgreSQL database. I've tried this command: ogr2ogr -f PostgreSQL PG:"dbname='OSM_DB' host='localhost' port='5432' user='postgres' password='postgres'...
6
votes
2answers
284 views

Upgrading multiple PostGIS databases?

I have several installs of Postgres / PostGIS, version 9.4 up to and including 9.6. I would like to upgrade all the databases (maybe 20 total) in the various installs to a single install of the ...
6
votes
2answers
1k views

Can QGIS cache a PostGIS layer for faster rendering?

Is there any setting that makes it possible to cache features from a PostGIS layer? (or any other layer) For example: I add 20 layers from postgres then for every one of them set symbology and ...
6
votes
1answer
2k views

How can I generate a specific number of random points in a polygon with PostGIS?

I have a postgis table that I've uploaded a shapefile to, this shapefile contains 200+ polygon rows with several integer columns. What I'd like to do is similar to the QGIS 'random points in polygon' ...
6
votes
1answer
408 views

How to Select all polygons or lines that lie at least partially within a circle with a radius?

I am storing OS Open Local data, as downloaded here: https://www.ordnancesurvey.co.uk/opendatadownload/products.html (top result). In particular my geometry column takes one of two possible shapes: ...
6
votes
3answers
3k views

Direct connecting to PostgreSQL from ArcGIS Desktop? [duplicate]

I am trying to connect to Postgresql database that I just installed on my server machine. As instructed by Esri I have download from postgres and installed psqlodbc. However, when I tried to add the ...
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: #...
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....
6
votes
1answer
132 views

Advance Digitizing tools with PostgreSQL views in QGIS

I have a PostgreSQL database which is accessed by multiple users and each user have different levels of privileges on tables and views. To insert/update/delete features, the users have to work on QGIS....
6
votes
2answers
223 views

How to calculate the points in polygon without overlap?

How to rank the circles that contain the highest number of points below. Problem is that I can't use the points in polygon tool because if a point is contained in multiple circle, then it shouldn't ...
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/...
6
votes
1answer
1k views

Data visualization and maintenance tool

Background Looking to leverage an existing GIS system for exploring organizational data. Architecture The following figure represents a high-level overview of the system's desired features: The ...
6
votes
1answer
1k views

QGIS WKT Raster Plugin error, what does this mean?

I have been playing with the WKT Raster plugin. I have installed the WKT Raster experimental binaries in PostGIS successfully and followed the instructions in the read me to the letter. When i am in ...
6
votes
2answers
2k views

Choosing datatype for holding Latitude, Longitude and Altitude in PostGIS?

I'm trying to take advantage of the many functions available in Postgis for dealing with latitude and longitude coordinate systems. However, I would like to add some altitude information to my ...
6
votes
1answer
299 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 ...
6
votes
1answer
281 views

Direct Connection from Postgres to ArcGIS results to two feature classes per table

I'm using ArcMap (10.3.1) to connect to Postgres (9.4.5), which has Postgis version 2.2.0 installed. Create/Enable Enterprise Geodatabase wasn't used in this database. It's just a connection to the ...
6
votes
1answer
298 views

Geometry not displaying correctly in QGIS 2.12

I loaded a few Shapefiles from different sources into a PostgreSQL database, when I visualize them in QQGIS some of them are not shown. This how i added the geometry column in my tables: SELECT ...
6
votes
2answers
731 views

Find start of river

Edit: Follow-up question here (= Find headwater polygons). How can I determine the start of a river in PostGIS? I have a river network (Multiline) and want to find the startpoints of the rivers. I ...
6
votes
1answer
2k views

Spatial database containing coordinates for stars in the sky?

My goal is to map the Skydome ( stars in the sky projected in earth surface ) using GIS. Has anyone done this before? I can't find any project close to this. What I just need to know is if there is ...
6
votes
1answer
504 views

Hitting Memory Limits of PostGIS ST_ConcaveHull / Incomplete Shrink Wrap

I am trying to use PostGIS 2.0 on Postgres 8.4 to do some mapping. I am working with the NOAA Shapefiles, specifically their US States & Territories file located here: http://www.nws.noaa.gov/...
6
votes
1answer
560 views

Importing a shapefile into Postgres: Can I use Multiple CPU Cores?

I'm importing some shapefiles into Postgres/PostGIS. I'm using Windows 8 and the shapefile importer/exporter GUI. It maxes out one CPU core. I'm wondering if there is a way I can use multiple cores ...
6
votes
1answer
1k views

PostGIS spatial reference ID (SRID) for regular cartesian coordinate system?

I would like to store a polygon as a set of 2D points represented as x, y coordinates of a Cartesian coordinate system. Which spatial reference can I use? Looking at my spatial_ref_sys table, all the ...
6
votes
1answer
818 views

Merge intersecting polygons in same table using Postgis

I am trying to merge polygons in the same table where the polygons intersect while preserving a unique id (can be any id from the features which intersect) in postgis. Currently, I end up with more ...
6
votes
1answer
1k views

How to export ESRI Geodatabase format .gdb from PostGIS

I found several posts explaining how to import a .gdb file into PostGIS. Is exporting also possible? For example using ogr2ogr to generate a .gdb file from query result.
6
votes
1answer
434 views

How to create ArcSDE Geodatabase in PostgreSQL without ArcGIS Desktop?

I am programming a small application using Arcobjects, and I need to create an ArcSDE geodatabase on PostgreSQL. In ArcSDE at 10.2 there is no Post Installation Wizard. How do I create a Geodatabase ...
6
votes
2answers
652 views

Error installing PGRouting database extensions

Im trying to install pgRouting and I cannot reach the end of the way because of the following error: psql:/home/user/share/postgresql/contrib/pgrouting-2.0/pgrouting.sql:2459: ERROR: could not load ...
6
votes
2answers
4k views

How to detect duplicates and keep only one?

I've got a table with lots of points (200.000). Some of them are duplicates (not exactly but e.g. a metro station has eight entrances) and I want to keep only one of these metro entrances. I am able ...
6
votes
2answers
412 views

Building spatialindex in Postgres with JNDI for GeoNetwork?

Which steps should be taken to build the spatialindex table inside PostgreSQL when using a JNDI connection? Is the table built by GeoNetwork or does it need to be created manually? What I have ...
6
votes
2answers
474 views

What controls the direction of TopoGeometry Linestrings?

In PostGIS, what if anything controls the direction that topogeometry LINESTRINGs will have when converted to geometry? It doesn't appear to be the order in which the edges are listed in the call to ...
6
votes
1answer
691 views

Maritime route prediction avoiding land masses

I am working on a project where the following functions have to be implemented: Predicting the locations of the ships (in maritime environment) at a future time. (Can be done with Kalman filter, IMM ...
6
votes
1answer
10k 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....