PostgreSQL is an open source object-relational database system.

learn more… | top users | synonyms (1)

1
vote
1answer
115 views

Looking for all points in a square around a given point in PostGIS

For a given point (x,y), I would like to find all the points in the neighborhood (defined by myRadius, a number, in meters). For now, I'm using a circle around each point, but some reason, I'd like ...
0
votes
2answers
191 views

I have a database in PostGIS, is possible using CartoDB? or I need another map server?

I have a data base in PostgreSQL/PostGIS, and I want to offer to my users a way of visualize it, Is it CartoDB the best option? It is possible? Or which is the best option around? (Please provide ...
4
votes
2answers
654 views

How can I query Postgres tables directly from OpenLayers?

I have a map that is being hosted with the components of OpenGeo. The map contains WMS layers of millions of parcels and some rail stops. All of our spatial data is on PostgresSQL. Right now the only ...
0
votes
0answers
44 views

I Can't Add More Layers on My WebGIS. Why?

I want to add layer on my web-mapping, I have this code in "pmapper_loc.map" LAYER NAME 'eastjv' TYPE POLYGON TEMPLATE "void" CONNECTIONTYPE postgis CONNECTION "host=localhost user=postgres ...
2
votes
2answers
493 views

I just want to make a 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 ...
0
votes
0answers
40 views

Grouping records by location

I want to group points together on the basis of their proximity to one another, within a certain range of proximity. I think that a good way to do this would be to parse the geometry to a new field ...
2
votes
2answers
457 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 ...
2
votes
0answers
42 views

What resources are there for PostGIS maintenance? [duplicate]

I am recommending the usage of a Postgres+PostGIS database to a client for storage of their Geospatial Data. I need to recommend the processes for maintenance of the database. The Client is an Oracle ...
0
votes
1answer
607 views

Building SQL statement in javascript and casting variables as strings

I'm working with Postgres 8.3/PostGIS which is storing some polygons. I'm writing a javascript function which will take in a parameter of a watershed id from another function. The javascript will ...
2
votes
1answer
64 views

Creating pop-ups on point layers

i have a postgresql database which is refreshed all 10 minutes and i need to create graphs in pop-up with the data. When i click, a pop-up (or annotation ?) appears. How can i make it possible ? ...
1
vote
3answers
607 views

Select empty Postgis (Postgresql) geometry column

I try to select rows in Postgis that have an empty geometry column. This works but gives me the opposite: SELECT * FROM foo where geom <> ''; And this fails: SELECT * FROM foo where geom = ...
1
vote
1answer
631 views

PostGIS - Geometry or Geography find points within distance in meters

I have a requirement where I need to store lat/long coordinates for locations all around the world. I then need to query for those locations that are within a specified distance (in meters) from ...
3
votes
1answer
153 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 ...
0
votes
1answer
202 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
0answers
53 views

pgRouting Win 7 installation problem

I have run into a problem installing pgRouting. Following Anita Grasers guide, I have gotten as far as to execute the three .sql files. The first sql file (routing_core) runs fine, but for the second ...
1
vote
0answers
203 views

Adding osm data to Postgres via Osm2pgsql : duplicate key value violates unique constraint

I'm trying to import osm global world data into a database created in postgreSQL but after a while of processing, I've got this error : Reading in file: E:\planet-latest.osm\planet-latest.osm ...
1
vote
0answers
55 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 ...
1
vote
1answer
83 views

Insert geometric data to a column from coordinate values in double precision

Screenshot of database http://wp.me/aIW9p-i0 For table “households”, I want to create two new columns: 1) “Geom”, data type – geometry; 2) “WKT”, data type ‐ text; I am intending to do the following ...
9
votes
3answers
3k 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 ...
0
votes
0answers
748 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
0answers
47 views

OSMOSIS tag filtering “OR” statement

I want to import only all ways with a specific tag OR all ways used by relations having a specific tag. With the following command, I only get ways with the tag and the relations but not the ways ...
1
vote
0answers
161 views

How to load the default layer style from Postgresql with Python?

I would like to write a python macro that upon opening my QGIS project file will reload the default style for each layer from the postgres 'layer_style' table. I have had a look at the documentation ...
4
votes
3answers
344 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
147 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
456 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). ...
0
votes
2answers
127 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
191 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
75 views

pgrouting shortest_path return longest_path

I've got troubles with shortest path function from pgrouting. I try to calculate the shortest path between an address and a bus stop. I first found the nearest road points for the address and the bus ...
-1
votes
2answers
367 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 ...
3
votes
1answer
196 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
77 views

Error creating Geoserver App-schema Store with Postgres: “AttributeTypeImpl cannot be cast to ComplexType”

I have this postgis table el_str( classid varying(70), meta_ist_data_cre date, meta_ist_data_mod date, meta_ist_ent_for character varying(100), meta_ist_livello numeric(15,0),meta_ist_origine ...
0
votes
1answer
138 views

AttributeError when I execute a python script

When I execute a python script to assign my database in postgres, I get this Error !! "AttributeError 'psycopg2._psycopg.cursor' object attribute 'execute' is read-only " def ma_fonction(d): # ...
2
votes
1answer
124 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 ...
0
votes
0answers
78 views

Query Multipolygon from PostGIS database

I my OSM PostGIS DB there are multiple polygons in the planet_osm_polygon table with the same osm_id, my idea is that this must be the parts of a Multipolygon. Now I want to query the Multipolygon ...
1
vote
1answer
241 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 ...
0
votes
0answers
610 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 ...
1
vote
1answer
103 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 ( ...
0
votes
1answer
65 views

How to select all cities in Germany from city and country layer?

I have 2 layers: layer_1: countries of europe, layer_2: cities of europe I want to create (and then save) a layer_3 which would be all the cities of Germany. Is there any good tutorial of how can i ...
1
vote
2answers
182 views

How to load layers from postgis to dotspatial or sharemap viewer

I want to develop a PostGIS viewer for PostgreSQL (with SQL query editor) to visualize PostGIS layers. Which spatial library (.net C# supported) do I have to use?
1
vote
1answer
1k views

How can I enable PostGIS Shapefile and DBF Loader 2.0 plugin in PgAdmin III?

I am on Windows and I want to download/enable the plugin "PostGIS Shapefile and DBF Loader 2.0". I installed PostGIS 2.0 using StackBuilder of PostgreSQL installer.
1
vote
0answers
39 views

How to load a raster from in-memory bitmap (not from file) into PostgreSQL

I have a bitmap in memory that I would like to load as a raster to PostgreSQL. An unfavorable option is saving the bitmap to file and then use raster2pgsql, but as we have several cameras that ...
0
votes
1answer
184 views

It seems there is a bug in St_Clip function of PostGIS

The code I am using for clip feature is : raster ST_Clip(raster rast, geometry geom, double precision[] nodataval=NULL, boolean crop=true); I tried clip a shape depends on the polygon from the ...
1
vote
1answer
568 views

Distance between DB field and Point using PostGIS

I'm using Postgresql 9.0 with PostGIS 2.0 extension. I've got the table and geographic field location: =# SELECT * FROM geography_columns; f_table_catalog | f_table_schema | f_table_name | ...
0
votes
1answer
207 views

ST_Touches for more than just one geometry

I'd like to select some neighbor polygons with ST_Touches. Therefore I found the following statement: SELECT b.name FROM layera as a JOIN layera as b ON ST_Touches((SELECT a.geom FROM layera as a ...
1
vote
1answer
57 views

Libraries to navigate and choose path based on congestion level?

I'm trying to develop a system to get best path to travel user based on road traffic (congestion level). Functionality of the system should be provide least congested path for traveler given ...
3
votes
0answers
214 views

Select features within a country or bounding box effectively using PostGIS

I imported the planet OSM file into a PostgreSQL 9.2/PostGIS 1.5 server using osm2pgsql. I now want to select certain map features which lie within the bounding box formed by a countries borders, e.g. ...
0
votes
1answer
274 views

SELECT assign_vertex_id('ways', 0.00001, 'the_geom', 'gid'); not work

I'm using using OSgeo 6.5 dvd and studing pgrouting workshop (link) for get shortest path. I created network topology call "pgrouting - workshop". There are three commands to execute in section 5.2 ...
6
votes
0answers
289 views

What is the correct syntax for using gdal_rasterize with a PostGIS datasource

I have a multipolygon layer in PostGIS and I would like to use gdal_rasterize to generate a raster. I cannot find the correct syntax to specify PostGIS as the data source. $ gdal_rasterize -a ...
0
votes
1answer
112 views

Solving flagrant shared object errors when compiling postgis 2.1SVN from source on Ubuntu 12.04

I have spent the past few days of my life trying to figure out how to get postgis to function properly with a non-standard directory installation of postgres 9.3, but have been unable to resolve the ...
1
vote
1answer
73 views

Tools to make buffer around the polygon on a web map?

We're starting web map app and still deciding what tools is the best to use. We would need option that user add some points and polygons, and also, to automatically get buffer around those polygons. ...