PostGIS is an extension for the PostgreSQL object-relational database that adds support for geographic objects.

learn more… | top users | synonyms

0
votes
2answers
76 views

Select area which is at least x meters away from other geometries

In my table I have the build-up area from a region as a big number of polygons. urban_area: id | name | geometry(polygon) I was wondering if and how I would be able to find the area within a ...
1
vote
1answer
195 views

Why ST_SRID cause Geoserver generated SQL query to run about 500x slower?

When investigating why Geoserver was so slow to render my WMS tiles from a POSTGIS database I found that the following Geoserver generated SQL query run almost 500x faster when remplacing the ...
2
votes
1answer
249 views

PostGIS ST_Contains query returns null

I have a shapefile of all US States that I've imported into a table with a multipolygon geometry column. (SRID 4326) I perform a spatial query based on lat/lon to return the state abbreviation. This ...
0
votes
3answers
883 views

Install PostGIS 2.0.3 on PostgreSQL x64 9.1 on Windows Server 2008 R2

I just try to install the PostGIS 2.0.3 on PostgreSQL x64 9.1 on the Windows Server 2008 R2. I got the error message "Error openning file for writing" C:\Program ...
2
votes
1answer
304 views

Why is ArcMap leaving connections “<IDLE> in transaction”?

The TL;DR version: When you use ArcMap Query Layers to access a PostgreSQL table, ArcMap's database connections are almost always sitting idle in unclosed transactions. Why would this be? I have ...
0
votes
1answer
155 views

Convert an area to meters² in PostGIS

I have this query : SELECT ST_AREA(geom::geography) FROM region The (geom) contains polygons from a shapefile imported into PostGIS with shp2pgsql. I've appended ::geography, else the result of ...
0
votes
1answer
605 views

error of data conversion value getting while importing shapefile in shp2pgsql-gui on ubuntu

Connecting: host=localhost port=5432 user=postgres dbname=tremblement_de_terre Connection succeeded. Connecting: host=localhost port=5432 user=postgres dbname=tremblement_de_terre ...
1
vote
0answers
267 views

Find area with highest relative density of one type of object

I'm using PostgreSQL/PostGIS and I have an advanced type of query. My database contains two types of object - let's say pubs and churches :) Is there a way that I can find the area with the densest ...
0
votes
1answer
81 views

Issues with pgrouting workshop code in postgis 2.11/pgrouting 2.0

I'm having issues getting the pgrouting workshop to work on Postgresql 9.2/PostGIS 2.11 and pgrouting 2.0 on windows. How can I rewrite the following php/sql code and make it compatible with my ...
8
votes
2answers
2k views

Install PostGIS and TIGER Data in Ubuntu 12.04

Could someone write a brief albeit dumbed down idiot's guide to installing postgis and loading national Tiger data on ubuntu? I've tried a few guides, namely, ...
0
votes
2answers
358 views

Intersect and MBR in POSTGIS and Mysql

I would like to know if POSTGIS and MySql with Spatial Extension use true geometry to find intersections/overlaps between geospatial objects. But i don't find it precisely in the documentation. I know ...
1
vote
0answers
543 views

postgresql 8.4 and postgis 1.5 error: type modifier is not allowed for type 'geometry'

I would like to generate a buffer (within a limit of 3km ) where the user clicked point. In pgAdmin, select my database and open a query editor and create 2 PostGIS functions (UTM Zone Function and ...
0
votes
1answer
95 views

Simplify table of linestrings

I have a PostGIS table of linestrings representing a road network. I want to simplify the geometries such that only lines that connect road junctions are stored. At the moment the table has a large ...
0
votes
0answers
220 views

Using Terraformer to convert to WKT

I'm using Terraformer to parse some ArcGIS JSON into a Terraformer primitive. I'd like to store the geometry in PostGIS for other purposes. So I'm trying to use the WKT.convert tool to convert this ...
0
votes
1answer
309 views

How to automatically convert line features into polygons using QGIS/PostGIS?

I have a master table of line features and a number of tables that I use to join (in the SQL sense) to produce various layers. Some of theses layers consist of boundaries of administrative zones ...
1
vote
0answers
120 views

Need to enhance a function for creating quadrants within polygons

I wrote a function that creates arbitrary quadrants within a polygon SELECT (quadrant(20,0.0,90)).* The first parameter is the polygon ID, the second one is the start angle and with the third one ...
1
vote
1answer
107 views

Get DISTINCT nearest neighbors with indexed search

I'm using the indexed nearest neighbor search to return the closest points to a given starting location. My points also have attributes like color and shape, stored as strings. I want each query to ...
2
votes
0answers
177 views

Trajectory Indexing: How could I store and index the large trajectory dataset generated by cars' GPS?

I have a large trajectory dataset generated by cars' GPS. Since I will do some data mining work on them, I need an efficient way to store and index them. I read some papers about Trajectory Indexing ...
1
vote
0answers
982 views

Creating a spatial database using EXTENSIONS: error loading legacy.sql

I am using Postgres 9.3 and PostGIS 2.1, installed in a Docker container via RUN echo "deb http://apt.postgresql.org/pub/repos/apt/ precise-pgdg main" >> /etc/apt/sources.list RUN apt-get -y ...
0
votes
2answers
511 views

Building CartoDB from source using updated dependencies on Ubuntu 13.10

I am attempting to build CartoDB from source on an Amazon VM for the first time in my life. For purposes of sanity, I want to use the same instance/database of postgres for both CartoDB and all of my ...
2
votes
1answer
444 views

creating a st_contains query with geographies not geometries

I'm trying to find all the geography points that are contained in a geography polygon, let say all the traffic lights within a given city or neighborhoods, how can i convert this working st_intersect ...
1
vote
1answer
705 views

Error while installing PostGIS on ubuntu 12.04

This is my first practice on Ubuntu machine. Here i am trying to install PostGIS 2.1 on postgresql server 9.3.2. When i am following the procedure mentioned here Help I am getting the following ...
2
votes
1answer
392 views

Geoserver configuration for large layers

I'm having problem with geoserver configuration. Currently I'm running geoserver 2.4 on CentOS and Tomcat 6 with 24 cores CPU and 48 GB RAM. I have uploaded 4 layers to PostGIS (PostgreSQL 9.3/PostGIS ...
1
vote
3answers
192 views

How to find area/multipolygon bounded by three other multipolygons?

Trying to get my head around PostGIS and QGIS...I'm trying to find the area (in white) bounded by three multipolygons (in purple) - refer to picture. Each multipolygon only touches the other two ...
1
vote
1answer
833 views

Can anyone explain me the TSP pgRouting function?

I have no problems using Dijkstra or A* functions, but when I try to run TSP, I really don't know where to start. What I'm, trying is to get the 'best route' to link four points (typical TSP ...
0
votes
0answers
196 views

issue with QGIS Scale Level

I have different layers of shape files. I have imported all shape files into PostGIS. Now i want to show 3 layers Country, State & District with different scale/zoom level I tried to set scale ...
0
votes
1answer
275 views

Cast record type to geometry type

How can I cast type record /subquery/ to geometry type? My example: select geom from (select (ST_Simplify..... ) as geom and result is type record, but I need to convert to geometry.
10
votes
2answers
1k views

What is the precision of SELECT DISTINCT on PostGIS geometry column?

I wonder what the precision of the SELECT DISTINCT operator is on a PostGIS geometry. On my system, the following query gives me a count of 5, which means that the inserted points are considered equal ...
1
vote
1answer
439 views

determining what polygons intersect another layer

I am attempting to establish what spatial relationship exists (if any) between two layers. I have two layers - one is a road network with a buffer of 500m, and the other is a collection of polygons ...
1
vote
1answer
733 views

How to improve speed of rendering polygons from Postgis in Leaflet?

I want to improve speed of rendering polygons in my Leaflet map. Polygons are stored in Postgis, and are rendered with select query with bbox. I wonder can I cache somehow those polygons? I read ...
3
votes
1answer
593 views

How to install PostGIS correctly with OpenGeo Suite 4.0 community edition on Windows 7?

I have installed the OpenGeo Suite community edition, version 3, onto Windows 7 multiple times in the past, with virtually no problems. With the latest version of OpenGeo Suite (4.0.1) (which ...
0
votes
3answers
299 views

Postgis ST_AsGeoJSON() to openlayers security issues?

Im about to implement a python script serving my openlayers code with GeoJson data from PostGIS. However, here How to connect OpenLayers to PostGIS data? I see " Unfortunately you cannot connect a web ...
1
vote
0answers
298 views

PostGIS Raster: raster2pgsql was not found in /usr/lib/postgresql/9.3/bin

I have installed sucessfully Postgresql-9.3-postgis on Ubuntu 12.04, following this instructions: http://trac.osgeo.org/postgis/wiki/UsersWikiPostGIS21UbuntuPGSQL93Apt Having done that, I have ...
1
vote
1answer
409 views

PostGIS ST_MapAlgebra Assistance

I would like to perform the equivalent of the below ArcGIS raster calculator equation in PostGIS using the ST_MapAlgebra function and return it as a tif using ST_AsTIFF. Con(("Sol_gain_clipped.tif" ...
3
votes
2answers
327 views

Find ordinal polygon points

How might I go about finding the following points of a MULTIPOLYGON in PostGIS? Northeasternmost Northwesternmost Southeasternmost Southwesternmost Can't seem to find a ST_ function to accomplish ...
1
vote
1answer
1k views

How to install PostGIS correctly with OpenGeo Suite 4.0 community edition on Ubuntu?

I have installed the OpenGeo Suite community edition, version 3 and 4, onto Ubuntu Server 10.04 and 12.04 multiple times in the past, with virtually no problems. With the latest version of OpenGeo ...
0
votes
1answer
2k views

How to resolve Geoserver ERROR: Operation on two geometries with different SRIDs

I am working on Geoserver Version 2.4.2 . I have one layer 'beat' in postgresql database. I have publish layer in geoserver but its not opening OpenLayers layer preview link. It gives error as ...
5
votes
1answer
277 views

How to transform a set of street-segments into city-blocks, with PostGIS2?

Theoretically is possible to obtain the polygons of city blocks (urban blocks) from streets, when streets are represented by street axis (LineStrings). The urban blocks are bounded by streets, so ...
0
votes
1answer
290 views

What can cause Geoserver to deliver a WMS tile so slowly?

I have a PostGIS database with a table containing about 300 000 simple polygons (table has spatial index). I have a Geoserver installed on the same server delivering this table with WMS. I have a ...
3
votes
2answers
1k views

Operation on mixed SRID geometries

[I'm new to the site and my English is poor, so it is difficult for me to explain my problem. But there is no other person I can ask.] My question is related to this exercise ...
10
votes
1answer
451 views

Postgis - extrude a polygon

I want to extrude a polygon shape in postgis to create a pseudo 3D effect. To this end I have written a crude function to achieve it. This is very much test code and it creates a new Y vertice for ...
1
vote
2answers
648 views

osm2pgsql error when import pbf for Nominatim DB

Good day to all, Am getting the following error when running utils/setup.php in nominatim 2.1: Reading in file: /storage/Library/geodata/africa-latest.osm.pbf Processing: Node(54854k 62.1k/s) ...
2
votes
0answers
683 views

import esri Personal Geodatabase (.mdb) to PostGIS

I would like to import an esri Personal Geodatabase (.mdb) to PostGIS. I have tried to follow the blog post: How to import esri Personal Geodatabase (.mdb) to PostGIS on Linux I installed the GDAL ...
0
votes
2answers
373 views

GeoAlchemy pg_function.geojson cannot be decoded with json.loads

I asked this quesiton over on StackOverflow and thought it might make more sense to post it here given it's GIS nature. Given this function: features being a list of SQLAlchemy objects template ...
-1
votes
1answer
105 views

postgis update geom from lat and long

I have a table that contains a lat/long column... i'd like to have a trigger that populate the geom column when a row gets inserted into the database. are there examples that I can look at?
6
votes
1answer
738 views

Feature Service from plain PostgreSQL/PostGIS in ArcGIS Server 10.2

Using 10.2 ArcMap/ArcGIS Server on Windows I've successfully published some map services from my PostgreSQL/PostGIS data and now I want to enable editing the features through a JavaScript web app. ...
1
vote
0answers
315 views

Write Nominatim-like geocode query using only PostGIS functions?

I'm new to PostGIS and was anticipating being able to write geocode queries using only the functions provided by PostGIS. However, once I loaded my database using osm2pgsql and discovered tables like ...
1
vote
0answers
167 views

identify subgraphs within planet_osm_line table

I want to identify subgraphs within the planet_osm_line table of the osm2pgsql-schema. those subgraphs are classified by certain tags i want to check. My first attempt was to do this recursively with ...
1
vote
2answers
461 views

how to build topology with 3d linestring in pgrouting

I have some 3d linestrings in postgis database, and I want to use pgrouting for calculating shortest path with Dijkstra. when I execute "select pgr_createTopology('route2', 0.000001, 'geo3d', 'id');" ...
7
votes
1answer
2k views

What is difference between ST_DWithin and ST_Distance for proximity search in PostGIS?

I have records stored in a table with latitude/longitude coordinates stored in a geometry field. I want to find all records nearby a user supplied reference point. Note "nearby" probably means less ...