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

learn more… | top users | synonyms

3
votes
1answer
560 views

For which operations a PostGIS Geography index is useful?

I need to do KNN- and within-region queries for POI data. I store the POI locations in a geography column. Since I understand that ST_DISTANCE doesn't use the geography index, I added a geometry index ...
3
votes
1answer
122 views

How to extract coordinate pairs of crossing points of two polygons in PostGIS?

Extracting coordinate pairs of crossing points of two polygons in PostGIS Is it possible to extract x,y-coordinate pairs of crossing points of two overlapping polygons in PostGIS? Suppose there are ...
1
vote
2answers
190 views

Cutting OSM roads in sections using PostGis

I'm a newby in postgis. I define a section by a part of a road that has no intersection. See image bellow: I'd like to extract all sections from all the roads of my postgis database. This gis ...
0
votes
1answer
329 views

'For each' type loop in postgres

I'm really struggling with optimizing one of my sources. Instead of doing all the request and foreach loops in my serverside application. I am thinking about shifting it fully to the database side. ...
0
votes
1answer
112 views

create node ids with a multilinestring

I've got to make a route with my multilinestring table "routebus", but I can't create the node ids... maybe the fact that it's a multilinestring is the problem, but I really don't know how to solve ...
2
votes
0answers
79 views

Optimize ST_CoveredBy performance

I have a PostGIS 2.0.3 table with 113,862 records with multipolygons represented both as geometry and geography. There is a gist index on each of them. I'm running the following queries: SELECT *, ...
3
votes
2answers
352 views

Extract PostGIS line features to GPX

I have a request to provide GPX files of certain trails (around 100) that are present in OpenStreetMap. Given a local PostGIS install with OSM data, I think the process would be something like: ...
1
vote
0answers
84 views

database-backed WMS service runs as transaction

I'm trying to tame a geoserver 2.3.2 installation (backed by Postgres 9.1 and Postgis 1.5) by determining which of the many, many layers are actually being used. To this end, I've set up an audit ...
0
votes
1answer
115 views

How to visualise “distance from amenities”?

I'm interested in calculating for every point the distance to the nearest amenity of each of a certain number of types (train station, supermarket and so on). I think the calculation will be ...
0
votes
2answers
279 views

Will mongodb simplify geometry like PostGIS?

I use the ST_Simplify() function in PostGIS, and am moving to a new geostack with mongodb. Does it have a comparable function?
14
votes
5answers
5k views

Which is the best way of working with PostGIS data in R?

I work with R almost all the time, and now I am using it for doing spatial data mining, but I have this question: I have a PostGIS database with (obviously) GIS data, If I want to make statistical ...
3
votes
1answer
1k views

PostGIS Dissolve/Union close polygons

I have approximately 1 million polygons that have been split by lines. However, this has caused one half of a polygon to be separated by approximately 50cm from its other half. After doing a lot of ...
1
vote
1answer
383 views

Split all OSM roads within boundingbox in 20m segments and save to new table

I have a PostGIS Database with all streets from osm within a given boundingbox. Now I want to split all roads from my table planet_osm_roads into segments of max 20 meters length and save those ...
2
votes
0answers
2k 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 ...
0
votes
1answer
181 views

Automatic four-colour (political boundary) rendering with PostGIS

This is probably a long shot, but is there a way to automatically select colours for neighbouring regions using the four colour theorem, in PostGIS? The data I'm using is suburb boundaries in ...
3
votes
3answers
619 views

Is it possible to check incoming GeoJSON to see if any of the features are identical to existing ones before adding to map?

I am working on a Rails/PostGIS app and doing a typical ajax call to retrieve GeoJSON within the map viewport bounds. Whenever the map is shifted or zoomed all of the markers are removed and then ...
1
vote
1answer
1k views

How to export from PostGIS to KML

What is the best way to export from PostGIS to KML using open source tools? Can I do it directly, or do I need to first export to shape files?
1
vote
1answer
86 views

Spatialite equivalent of ST_MakeBox2D?

I'm a GIS newbie. I'm porting an app that uses PostGIS to SpatiaLite and wonder what the SpatiaLite equivalent of ST_MakeBox2D might be. Can someone point me to an example?
1
vote
1answer
98 views

PostGIS integer loop

PostGIS is very new to me, and I'm having difficulty performing an operation that probably isn't too difficult. I have vector layers each filled with the outline of a landmass through time. I want to ...
3
votes
1answer
498 views

Problems saving moved points via OpenLayers

I am trying to implement what is described by @underdark in this post but have hit a snag. I have a point layer stored in PostGIS and served via GeoServer to a webpage using Openlayers. The data ...
3
votes
1answer
90 views

Should states/provinces be stored as geographic or geometric data types?

While reading the GeoDjango documentation, they advise using geometric rather than the geographic data type when storing values that are on a continental scale. The Canadian province of Ontario, for ...
4
votes
3answers
938 views

generate spatial geom from lat lon points for map in PostGIS

I am interested in a PostGIS implementation of the R-based soulution from a previous question, How can I convert data in the form of lat, lon, value into a raster file using R? Currently, I am hoping ...
1
vote
1answer
306 views

Shortest path for a given waypoints

I have an application where the user selects some "markers" from a google maps (all those coordinates' markers are stored into a postgresql database). Right now, I can draw the route between all ...
1
vote
2answers
908 views

How to connect Dotspatial with PostGIS/PosgresSQL database and perform SQL function

I am currently doing a project that needs a GIS front-end interface to visualize geographical objects (road networks), and a back-end geo-spatial database to implement network analysis (SQL based). ...
1
vote
1answer
171 views

postgis: problems creating flow direction polyline; not all needed connections are drawn

I am using post-gis to create a polyline that describes the flow direction of water in a landscape/catchment, to use in a hydrological model. To do that I start with a regular grid (as polygon) and ...
3
votes
1answer
86 views

Force a collection of unknown number of points to be a polygon

I am working with point clusters and want to create the rough shape of the cluster in the form of a polygon using ST_ConvexHull(). I'm having problems with single outliers though, as they don't ...
0
votes
2answers
509 views

Extract lat/long from point type column

In my table I have a column "location" that is a POINT. I tried this: SELECT ST_X(location), ST_Y(location) FROM locations; But I still get the error message: ERROR: function st_x(point) does not ...
6
votes
1answer
318 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 ...
0
votes
1answer
209 views

pgrouting 2 pgr_createTopology not populating source and target field

I trying to integrate pgrouting 2.x. I am now using pgrouting 1.x. In version 1 I used assign_vertex_id. It already dropped in version 2 and became pgr_createTopology function SELECT ...
0
votes
2answers
1k views

What is gin_trgm_ops of postGIS

I am using a query where the following line written CREATE INDEX trgm_idx ON public.roads_r USING gin (name gin_trgm_ops); Its returning the following error operator class "gin_trgm_ops" does ...
0
votes
2answers
474 views

PostGIS ERROR: function x(geometry) does not exist

I am using the following postgis query for updating a pgrouting table from my custom road data. In the x(ST_StartPoint(part_geom)) as x1, line its returning an error mentioned ERROR: function ...
4
votes
1answer
434 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 ...
0
votes
1answer
316 views

Why postgis st_overlaps reports Qgis' “avoid intersections” generated polygon as overlapping with others?

I am implementing a postgis trigger on polygon layer to check and reject newly created polygon if it overlaps others. The problem shows when I digitize new polygons adjacent to others in Quantum GIS ...
0
votes
1answer
201 views

Circles created by points inside bounding box query - PostGIS

The table that i want to perform this query has 3 columns: id, the_geom and seen_count. The query i've been using to get points inside a bounding box is as follows: SELECT id, ST_AsGeoJSON(the_geom) ...
3
votes
1answer
547 views

A GeoServer layer is based on PostGis view, but featureID is random (PK is ignored)

I have a layer in GeoServer. It is based on PostGis view. It seems that GeoServer generates random featureIDs. The view has the same columns as the underlying table, including the PK column. But ...
0
votes
1answer
4k views

Import Error in Postgis: AddGeometryColumn does not exist [closed]

Importing with configuration: ind_adm2, public, geom, C:\Program Files\OpenGeo\OpenGeo Suite\webapps\geoserver\india map\IND_adm2, mode=c, dump=1, simple=0, geography=0, index=1, shape=1, srid=0 ...
1
vote
2answers
733 views

ST_DWithin uses Geometry or Geography type

assuming I use the following query to match the locations to a poi within 10km distance: SELECT poi.poi_id, h.loc_id, (ST_Distance(poi.geom,h.geom)*111.111) as distanceinkm FROM iut_poi_transl AS ...
0
votes
1answer
86 views

Anyone recognize this (reference-system related?) pattern of distortion?

I'm new to GIS and was making some progress until getting rather stumped on this one. I've imported what I believe to be lat/lon data based on US FIPS county codes in weather alerts (NOAA SPC). When ...
2
votes
2answers
264 views

3D KNN index in PostGIS

I am looking for a way to do fast KNN queries on a dataset of 10+ million 3D points (and growing). I was hoping PostGIS would do the trick with its new <-> and <#> operators, but they don't seem ...
2
votes
1answer
324 views

Are there Open Source alternatives to ArcGIS Aggregate and Extract Value to Points?

I am looking for some kind of Open source GIS library which can perform spatial analytic functions same as ArcGIS. My use case is : I am processing GeoTIFF file provided by USGS and performing ...
3
votes
3answers
557 views

Why is sum of st_length() of segments 20% too big?

I'm running a query to return the total length of some bike paths, from OpenStreetMap. Compared to the known actual length of these paths, they're all a bit too big - something like 10-30%. I know it ...
3
votes
1answer
649 views

Auto-increment id when adding a feature to a PostGIS layer in QGIS

when I enter edit mode of a PostGIS layer in QGIS 1.8 and add a feature, is it possible to auto-increment the ID? I always have to look what last used ID was and manually enter a new one. I can't ...
2
votes
1answer
467 views

Find nearest neighbours faster in PostGIS

I currently have a query that calculates the number of useful amenities within a certain radius of each town, using OSM data: create table places as select town.name, place, town.way, ( select ...
1
vote
0answers
2k views

How to write a point-in-polygon query for Postgis?

I'm trying to write a query in Postgis which will return all rows where a dynamically changing longitude/latitude point resides within the geometric polygon of that row (in the geom column). This is ...
0
votes
2answers
90 views

SQL query for raster point samping within postgis

I am trying to update my table with the values from a raster stored within the database. Edit: I've posted the answer bellow: http://gis.stackexchange.com/a/63655/6711 The query I'm trying to use is ...
1
vote
1answer
134 views

Postgis max contains query

I have two tables: CREATE TABLE periphereies ( gid serial NOT NULL, per character varying(60), geom geometry(MultiPolygon), CONSTRAINT periphereies_pkey PRIMARY KEY (gid) ) WITH ( ...
2
votes
2answers
431 views

Problem with PostGIS in Opengeo Suite in Ubuntu 13?

Everything is working fine after installing the Opengeo Suite in Ubuntu except PostGIS issues. In the dashboard the PostGIS manage is disabled(grey) but PostgreSQL is working via pgadmin3. And also ...
0
votes
2answers
558 views

Mapserver mapfile with multiple layers that pull from postgis only gives broken image

I'm trying to use this mapfile that I made: MAP IMAGETYPE PNG EXTENT -90.000124 41.000032 -88.000044 42.000035 SIZE 1600 800 IMAGECOLOR 255 0 1 OUTPUTFORMAT NAME png DRIVER AGG/PNG ...
1
vote
2answers
64 views

Insert into X coordinate separately?

I have a postgis table with a point geometry for lat/long but i want to run a query that inserts a new row with lat, long values separately instead of concatenating them into a geometry. so is there ...
1
vote
0answers
59 views

Query OSM ids by name in PostGIS

I installed PostGIS on a server and now I want to query the OSM ids referring to a name, in order to get a list of all geometries according to this name. Is there an efficient way to query this ids ...