PostgreSQL is an open source object-relational database system.

learn more… | top users | synonyms (1)

1
vote
1answer
449 views

PostGIS package problem on Ubuntu 12.04.2 LTS

I've started getting an error on Ubuntu when trying to upgrade packages: "Not all updates can be installed Run a partial upgrade, to install as many upgrades as possible" When I run a partial ...
3
votes
1answer
1k views

Setting coordinate system in PostGIS

The geographical information data that INEGI (part of the Mexico's goverment) provides are in the following coordinate system (or is projection?) Lambert Conformal Conic, double standard parallel ...
0
votes
0answers
15 views

postgres on bash command line and conversion to python with parameter substitution [duplicate]

i can use the pgsql2shp on the command line but however if i try to incorporate it into a python script with parameter substitution it fails. how best could I improve this. This is for a postgres ...
2
votes
1answer
156 views

Postgis and python parameterised queries

I have a python script that i am trying to run in order to dump a postgres table and I noticed there is a pgsql2shp command in bash to perform this easily but I would like to incoperate the same ...
0
votes
1answer
142 views

Find closest NON interpolated point on a LINESTRING

I have a linestring and a point, I want to find the closest point on the linestring to that point. Below is a screenshot of a test I created to confirm that the various POSTGIS functions for finding ...
1
vote
1answer
224 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 ...
1
vote
2answers
671 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
2k 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 ...
2
votes
2answers
4k 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 ...
2
votes
2answers
2k 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 ...
1
vote
1answer
2k 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
283 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
2k 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 = ...
2
votes
1answer
2k 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
596 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
348 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
67 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
390 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
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 ...
1
vote
1answer
125 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 ...
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 ...
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 ...
1
vote
0answers
321 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
998 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
321 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
929 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
578 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
326 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
124 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
vote
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 ...
4
votes
1answer
442 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
141 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
568 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
2answers
271 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
486 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 ...
1
vote
1answer
135 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
109 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 ...
2
votes
2answers
443 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
3k 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
52 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
107 views

Creating plugin in QGIS to access PostgreSQL? [closed]

i want to develop a plugin in QGIS to access to my database in postgres : i want to import a file.sql that contains my SQL Code in a textbox , and execute this code to assign myd database.
0
votes
1answer
250 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
2k 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
2answers
789 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
66 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
450 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
631 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
523 views

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

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 ...