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

learn more… | top users | synonyms

2
votes
1answer
59 views

ST_Difference on linestrings and polygons slow and fails

I am trying to create a new table of linestrings with their existing attribution that are "different" to some polygons and am struggling to get a working PostGIS query. I have a table of linestrings ...
4
votes
1answer
247 views

Solving “Traveling Purchaser Problem”

Till now, I was using PostGIS-pgrouting pgr_tsp function to solve Travelling Salesman Problems. This function can give us the best possible route depending upon the cost parameter when the starting, ...
1
vote
1answer
220 views

Is there any method to retrieve SQL queries into a GeoJSON file using JSP?

I want to display the contents of my PostGIS database of various shape-files in leaflet pop-up dynamically. I know there are static methods available that retrieves attributes. But I need to by ...
1
vote
1answer
196 views

Error when trying to visualize geography data with QGIS

I use PostGis, and want to use QGIS to visualize data. But I get error message when trying to show the points in the table. I create a table: CREATE TABLE poi ( id text NOT NULL, coord ...
1
vote
1answer
114 views

Postgis How to populate column with geography data from geometric data?

I stored my point date in geometry format with SRID 4326 supposed EWKB (data looks like this "0101000020E610000071F1A611E9392640AECD10D8E68C4A40") = way geometry(Point,4326) I want to have the ...
1
vote
1answer
55 views

Postgres/PostGIS calculate area of Polygon in Square Miles

Polygon in Postgres/PostGIS database. It has the spatial reference 4269 which is in degrees. I know there is a ST_Area() to calculate it. this is my query to select the areas in the SRID units. ...
1
vote
0answers
23 views

Missing vector features when import from .sqlite to postgis?

I imported vector data from a .sqlite file to PostGIS but not all of my vector features imported. I haven't been able to determine a reason for most of the loss of features between the databases ...
0
votes
1answer
41 views

Python to Postgres insert Geometry syntax

Having trouble creating geom columns and inserting geometry lat lng values into postgres table Is my syntax correct in creating the point geometry fields? st_makepoint is the point constructor. ...
2
votes
0answers
21 views

Kernel Density with vector output and/or possible automation?

I'm currently working on a project that will have me calculate the kernel density of a number of shapefiles with varying parameters. My current workflow goes like this: Import shapefiles in QGIS ...
1
vote
1answer
50 views

Making selections from two tables based on the reference table in PostgreSQL/Postgis

Based on the address locations ad for the region of interest (as a reference), I am trying to make selections from two street layers street1 and street2. What I have tried is shown by the code below: ...
0
votes
2answers
277 views

PostGIS pgr_dijkstra function does not find node that appears in ways table

I am having a problem with pgr_dijkstra algorithm not finding some of the node vertices in my table. I have a large dataset of survey travel diaries and am trying to determine their shortest paths ...
0
votes
2answers
31 views

Proper handling in PostgreSQL/PostGIS only after many queries?

I am trying to analyze GPS data of a parade on a fixed route with a certain variance due to the urban surrounding (canyoning). I'd like to project the GPS waypoints to the nearest points on the route ...
1
vote
1answer
23 views

Postgres/postgis st_makepoint

I am looping through a csv, taking latlng values and trying to add there geometry to postgis enabled postgres table. xy values are int's conn = psycopg2.connect("connected") cur = conn.cursor() ...
2
votes
1answer
172 views

ST_Within for TIN or 3D objct in PostGIS

I would like to query if a point is located in the TIN object. My TIN object is closed (convex hull) object (i.e. surface of a round object). I tried to use ST_Within, but the error message returns ...
0
votes
1answer
25 views

lwgeom_to_geojson: 'PolyhedralSurface' geometry type not supported

I am using PostGIS (PostGIS 2.2.0 with SFCGAL extension) and I have data in postgres which is loaded using citygml2psql tool and it contains polyhedralSurface geometry. But when i run query : ...
1
vote
0answers
13 views

Single WMS pgRouting Layer from Array of Lat Longs

I am using the following loop for displaying the a bus route. 'stopLonLat' is a JavaScript object with all the latitude and longitude values of various bus stops. Since I am not looking for shortest ...
-1
votes
0answers
18 views

Create a trigger for postgresql to populate a state field

We have a Postgresql SDE database that we would like to automatically populate certain fields in the attribute table when a new feature is added like State (based on location), user info and time. I ...
0
votes
0answers
18 views

Not able to edit PostGIS SDE featureclass in ArcGIS Standard

I have created an Enterprise geodatabase in PostgreSQL and now uploading OS MasterMap data using QGIS OS Translator Tool. The feature classes created are not editable, however if I create a blank ...
1
vote
2answers
742 views

ERROR: Operation on two geometries with different SRIDs

I'm new when it comes to GIS and PostgreSQL. I have searched everywhere for the cause of this problem but I can't find the solution. I was following the documentation here First i created a table: ...
2
votes
2answers
36 views

PostGIS distance between multiple points

I have a data set in a PostGIS data base (table name is animals) with the following structure: id (int, pk) | latitude (double) | longitude (double) | type (char) | geom (srid: 4326) In the ...
0
votes
1answer
210 views

What is the difference between ST_GEOMETRY and WKT?

What is the difference between ST_GEOMETRY and WKT? Is ST_GEOMETRY just a database type and WKT (well-known text) is a textual representation of the data?
1
vote
0answers
12 views

How to query raster in PostGIS within GeoJSON mask only

I have a raster in PostGIS which I need to query pixel values from, but only within a specific area. So I have a GeoJSON polygon within which I want to extract min/max/avg values of the raster from. ...
3
votes
1answer
38 views

pgrouting: how to temporarily increase some edges' cost without affecting concurrent queries?

How to temporarily increase some edges' cost for one query without affecting other concurrent query? The case is, I would like to develop a system that allows users to query the shortest path of a ...
3
votes
2answers
55 views

PostGIS - Get a point inside a line or polygon

I need to get the center point of a line or polygon to make marker for my app. So that when you click a marker the geometry appears (line or polygon). I used ST_Centroid to make it work. The result ...
0
votes
0answers
22 views

Problem to input a .osm file in a Postgres database with Osmosis

I have a problem with import a *.osm file to postgres/postgis database. I hope someone can help me? Ich have done the following things. install Postgresql/Postgis and pgAdmin III -> works fine ...
1
vote
0answers
22 views

How to pass time dimension to postgis DB layer WHERE clause in Geoserver?

I have successfully configured a layer with time dimension that is backed by an SQL query to a PostGIS DB. I can query it through WMS, but I just can't find a way to pass the start and end time to the ...
8
votes
1answer
452 views

How to fix “Raster layer: Provider is not valid” error when loading PostGIS rasters?

I compiled and installed the current QGIS master as usual and I imported some raster data into my postgres 9.3 database with postgis 2.0.2 extension. Whenever I try to import the raster data into qgis ...
1
vote
1answer
18 views

How to style feature in mapserver mapfile based on values from database?

We have a table "Shapes" in PostGIS that consists of (among other things) a geometry column (the_geom) and an ID column (type_id) which is a foreign key to another table called "ShapeTypes". A given ...
1
vote
1answer
19 views

Geometry column of new feature is not stored

var saveStrategy2 = new OpenLayers.Strategy.Save(); var vectorLayer = new OpenLayers.Layer.Vector("Vectors", { strategies : [new OpenLayers.Strategy.BBOX(), saveStrategy2], protocol : new ...
2
votes
2answers
60 views

postgis table - shapefile - mapinfo file [closed]

I have a .tab file with a ridiculous amount of columns (96). One of my colleagues at my new job wanted to update them manually one by one. I told him I could do that in an easier way using PostGIS. So ...
0
votes
2answers
448 views

Starting Tile Sever Rendering Error: OSMBright.xml

I was following the entire process found here: https://switch2osm.org/serving-tiles/manually-building-a-tile-server-14-04/ I have an error though on Testing your tileserver part where i run the ...
0
votes
1answer
397 views

GeoDjango: Find all points within radius?

I am working in GeoDjango (with a Django 1.7 and PostGIS 2.1.4 backend). I have a model with a PointField in my database and I am trying to find all items within 10km radius of a point. Here is my ...
1
vote
0answers
47 views

importing shapefile in postgres

I am trying to import a shapefile to my database in postgres but there is no shapefile loader appearing in the plugin menu in PgAdmin. Postgis is definitely installed as I have added it as an ...
2
votes
2answers
425 views

Store GPS trace with timestamps, then interpolate location by time

I'd like to: Load a 1-second interval GPS trace (not GPX) into PostGIS Then query the table at sub-second accuracy (about 5Hz), and get PostGIS to interpolate the location by time. Is this ...
3
votes
1answer
50 views

GIST slows down query?

I have an odd case where creating a GIST index slows down my query instead of speeding it up. The setup consists of two tables, one with multi-polygons, another with points. I want to calculate which ...
1
vote
0answers
22 views

How to get all pixel values of raster within a GeoJSON FeatureCollection in PostGIS

I need to extract all pixel values in a raster within a GeoJSON mask, using ST_Intersects. Attempt (throws invalid GeoJson representation): select row_to_json(t) from (select * from table_name where ...
0
votes
0answers
24 views

Problem with projection in pgRouting layer in GeoServer

I followed this tutorial for creating a pgRouting layer in GeoServer. When I try to publish this layer Native Bounding Box and Lat/Lon Bounding Box are not getting generated correctly. For my road ...
2
votes
1answer
36 views

Export to DXF from PostgreSQL using ogr2ogr

I have a large dataset in a PostGIS database (OS MasterMap). My aim is to export specific areas of this dataset to DXF, while retaining the attributes of the data as layers in the DXF. My current ...
1
vote
1answer
37 views

Get points from polygons with extreme angles

I intend to remove points from a polygons with extreme angles like the image link Get the points Remove from linestring I make my buffer like this... SELECT ...
0
votes
1answer
131 views

PostGIS broken after Ubuntu 16.04 upgrade

I upgraded Ubuntu from 15.01 to 16.04 and PostGIS is broken. Below are the errors: psql (9.5.2) Type "help" for help. osm=# select version(); version ...
2
votes
2answers
314 views

How to get Geography data to GeoJSON in PostgreSQL?

I am using PostgreSQL database and there is a field in a table(area), i.e., public.geography(Points,4326). It contains below type of records "0101000020E6100000BA83D89942334540780B24287ED051C0" I ...
1
vote
1answer
953 views

Import shapefiles into PostGIS using Python with psycopg2

I want to import shapefiles to PostGIS using Python with arcpy & psycopg2 to connect to PostGIS. I don't want to use the existing shapefile loader nor ogr, just write my own import tool. My steps ...
1
vote
0answers
23 views

QGIS - Split a line having attributes with UNIQUE constraint

I am using QGIS with a PostgreSQL/PostGIS database, where the lines table (named 'segments'), besides the integer serial primary key, has a UUID column having a UNIQUE constraint. Trying to split a ...
1
vote
0answers
21 views

Modifying PostGIS database using geoserver

I want to build a mobile app using GeoServer and PostGIS database and I am little confused about how user will interact with database. I want end users to be able to upload new geospatial data to the ...
1
vote
0answers
29 views

Generate more nodes for routing

The following picture shows my routing database all set for querying loaded to QGIS. Red line is the road I want to select route from, yellow points are the vertices generated after running ...
0
votes
1answer
20 views

ST_Length(ST_ShortestLine()) equals 0, but ST_Disjoint = True

Recently, I encountered a very strange phenomenon. I have to split a multilinestring on the point of touching another line, sounds complicated but it is just preprocessing to build a graph. Now the ...
15
votes
7answers
18k views

How can I enable Shapefile GUI Loader in pgadmin3?

I have installed PostgreSQL 9.1 with PostGIS 1.5.3 on Ubuntu 11.10. I also installed pgadmin3 1.14.0-beta1, following this instructions. If I remember correctly, when I installed PostGIS on Windows ...
17
votes
2answers
186 views

Best-practices for databases and APIs with geographic data spanning the antimeridian

What is the best-practice for storing geographic features (lines, polygons and their multipart equivalents) when these features span the antimeridian (±180° longitude), and need to be sent to and ...
3
votes
1answer
188 views

GROUP BY on geography in postgres 9.4.0 returns wrong/different results

table definition: CREATE TABLE search ( id bigserial NOT NULL, location geography(Point,4326) NOT NULL, location_aggregated geography(Point,4326) NOT NULL, created timestamp with time zone ...
2
votes
0answers
12 views

ST_ColorMap wrongly loses 0.0 value when INTERPOLATE

I want apply a colormap to a monoband grey scale tiff from postgis calling ST_ColorMap. My query is something like "SELECT oid, lowrite(lo_open(oid, 131072), tiff) As num_bytes " + "FROM ( ...