PostgreSQL is an open source object-relational database system.

learn more… | top users | synonyms (1)

0
votes
2answers
287 views

How to add other column into pgrouting query result in pgAdmin output pane?

I'm working on my final research project now and I'm a new user in pgrouting. Is there any chances to add another column name to display in the output pane? I'd like to add road name column so that I ...
0
votes
1answer
511 views

Problems while installing Postgres [closed]

I was trying to install Postgres several times, but it didn't work out. I am using Mac and I tried to install Postgress.app, I got problems with database connection. I decided to install Postgress ...
2
votes
2answers
970 views

How to find the row that has a shape file containing a lat long point in my PostGIS geometry column

I am fairly new to PostGis and I'm trying to return a row in my table that has the shape file containing a lat long point I pass in via st_contains. Below is a query I wrote myself that brings back 0 ...
1
vote
2answers
726 views

Problem Importing shapefile into postgresql with simple geometries

I have a Line Type shapefiles to be imported to postgis database on my postgresql, I'm using the SHP loader, I checked the "Generate simple geometries instead of MULTI Geometries" options so that the ...
0
votes
1answer
209 views

pg_escape_string() equivalent?

A client is hosting one of my maps on a server that does NOT have a postgres module installed, and will not install it (nor anything else... ) . We have been told also by the site owner that we can't ...
1
vote
0answers
58 views

Create a single line from a resultset of line segments

How can I create a single line using the line segments geometry found in each row in resultset? I followed this tutorial: http://anitagraser.com/2013/07/06/pgrouting-2-0-for-windows-quick-guide/ I ...
5
votes
3answers
6k views

What's the fastest way to do a bounding box query in PostGIS

I have a Postgres table, with almost 2 million rows, with a long-lat coordinates field in the form POINT(-73.4938 33.2405). Supposing there's a geospatial index on that field, what's the most ...
0
votes
0answers
292 views

Mapscript. Converting pixel coordinate to latitude longitude for postgis

I have a map created with mapscript. It is a 2D (PNG format) that's being shown on a web browser. I want to get the lat/long coordinates whenever the user click on the image, and then save it in my ...
1
vote
1answer
215 views

Creating a new time column in PostGIS / CartoDB

I received a CSV file that has year (just two digits, '70'), month (as 'JAN'), day and time (as '0310') as separate columns. Bringing in to CartoDB / PostGIS they all appear as strings. I'd like to ...
3
votes
1answer
952 views

Access Shapefiles from PostgreSQL/Postgis in R

I successfully setup a connection to my PostgreSQL/PostGIS database on Ubuntu Server from my Laptop (Ubuntu 12.04). Therefore I installed RODBC in R to setup a connection and I made a correct entry ...
1
vote
1answer
216 views

Import OSM data into ArcSDE using osm2pgsql

I am trying to import OpenStreetMap (osm) data into a PostGRES database using OSM2PGSQL, and then I need to convert that data so I can use it in ArcSDE/ArcGIS Desktop 10.2. Here is what I do: ...
9
votes
2answers
4k views

Import large CSV file into PostGIS

I am trying to import CSV files into PostGIS. Following this post, I have created tables before. I found other suggestions saying that I can run the copy command. If I run this command: COPY table ...
1
vote
1answer
803 views

How to install pgrouting on Postgresql 9.2 located at /opt/PostgreSQL/9.2/ on Ubuntu Server 12.10?

I have installed Postgresql 9.2 on Ubuntu Server 12.10 using one-click installer from the EnterpriseDB. Then using the StackBuilder I installed PostGIS. Now I also want to install the pgrouting ...
0
votes
1answer
38 views

Postgres Data insertation [closed]

I have a table with 4 column. There are thousands of data. The column names are serial || name || X || Y From 500 I made a mistake and put the X data into Y field and Y data into X field. So can I ...
5
votes
3answers
471 views

GIN/GiST Full Text searches through openlayers, geoserver and postgres

I have a a web app that automatically maps recent tweets and allows you to search for certain keywords. it uses Postgres for the database, geoserver as the server, and openlayers as the cartographic ...
2
votes
1answer
5k views

Trouble with INSERT in PostgreSQL 9.1

I am trying to execute the following: INSERT INTO lithic (cp_east, cp_north, type, material, art_count, comment, field_no, gps_unit, initials, rec_date, cortex, geom) SELECT (cp_east, cp_north, ...
4
votes
1answer
185 views

Insertion of a raster geotiff in PostgreSQL database

I am new in PostgreSQL and psql. What is the method to insert a raster geotiff in a postgreSQL database using command psql? What is the best format? Someone indicates to save a raster image as BLOB ...
2
votes
2answers
186 views

shp2pgsql mangles date field — quotes data as if it were char or varchar

I'm attempting to import a shapefile into postgis via shp2pgsql. Among the fields in the shapefile's .dbf file is a date field with format yyyymmdd, which is a valid date format for postgresql. ...
0
votes
2answers
77 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 ...
2
votes
1answer
319 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
0answers
234 views

error occured when importing file on cartodb local host

Logged in as development user. Not able to import a .zip or a csv file. Ruby console has this error log 1.9.2p320 :004 > DataImport[1].log 2014-01-07T05:44:44Z || Using database connection with {...
8
votes
2answers
3k 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, http://wiki.bitnami.com/@api/deki/pages/...
0
votes
1answer
123 views

INSERT INTO Error

Hey guys I am trying to get the results of a query into a column in my table. The error I received is this ERROR: Quota exceeded by 16KB. I am using cartodb API, INSERT INTO address (distance) ...
1
vote
0answers
581 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 ...
1
vote
0answers
1k 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
533 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
477 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
99 views

python scripts for accessing info from a postgresql database in case of a qgis application?

I am beginner and I need to know if I could access a postgresql database from python script made for a qgis application
1
vote
1answer
284 views

Ogr2ogr - shape to postgis - is there a way to use and preserve an existing primary key?

I've been struggling with this for a while, tried a few different ideas inspired by these pages: http://www.gdal.org/ogr2ogr.html http://www.gdal.org/ogr/drv_pg.html http://www.gdal.org/ogr/...
0
votes
1answer
313 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.
3
votes
1answer
769 views

How can I decrease size of geodatabase “Nominatim”?

I downloaded Nominatim http://wiki.openstreetmap.org/wiki/Nominatim/Installation. The database size is more than 400 GB. The speed of geocoding is very slow. I want to increase it. For example, I ...
0
votes
1answer
703 views

How to install pgrouting 2.x on ubuntu 12.04 LTS

I try to install pgrouting 2.0 on ubuntu 12.04 LTS with postgresql 9.2 I have ppa in the system by sudo add-apt-repository ppa:gwibber-daily/ppa answer: `You are about to add the following PPA to ...
1
vote
2answers
697 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) Way(...
2
votes
0answers
182 views

Data loss after compressing geodatabase in ArcSDE

I suffer sometimes from data loss after compressing ArcSDE geodatabase, this geodatabase contains versioned feature classes, so the compressing script reconciles and compress the geodatabase: # ...
0
votes
2answers
404 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 ...
3
votes
1answer
857 views

Retrieve geographic coordinates from OSM imported data

We have just finished importing OSM data into our PostGIS (Postgresql) database. And we are having difficulty understanding the geometry of various shapes imported. We run a query to retrieve the ...
3
votes
1answer
142 views

Add vertices costs to postgresql routable network

In osm2po it's possible to create custom routers as the sample router TrafficSignalsRouter, but the costs seem to be added on vertices after the routing. Is it possible to add these costs on the ...
4
votes
1answer
1k views

How to edit PostgreSQL geodatabase table using ArcPy?

I'm trying to update a table using a python script and it's not working. The table resides in a postgres geodatabase and is versioned. I have also verified my user permissions (I'm the admin). The ...
8
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 ...
0
votes
0answers
68 views

Unable to set pg as default backend with osgeo4w GRASS

I am trying to set PostgreSQL as default backend for GRASS, but cannot manage to get it right. Working with GRASS 6.4.3 print g.run_command('db.connect', driver='pg', flags = 'c', database="host=...
1
vote
4answers
68 views

postgresql how not to show the first result returned st_distance

I have a query that calculates the distance away from a given point, however currently my query returns the point I am actually querying a distance for. How would I prevent site 1 from showing in my ...
8
votes
1answer
237 views

What spatially-aware GUI administration softwares exist for PostgreSQL/PostGIS (free or paid)?

Most of my experience with PostgreSQL has been PGAdminIII and psql, but I'm interested to know what others use. Is there PostgreSQL equivalent to SQL Server Management Studio (SSMS) and its ability ...
2
votes
3answers
4k views

Unable to connect PostgreSQL to ArcMap 10.1

I'm trying to connect my local PostgreSQL database to ArcMap 10.1 but for some reason I'm unable to connect to the server. Any ideas of what might be causing the issue. For the instance option in ...
0
votes
1answer
330 views

Install both postgis and postgresql-9.1-postgis packages?

I've installed the PostgreSQL package (ver. 9.1) on a Debian 7 server using the command "apt-get install postgresql postgresql-contrib". I now want to install PostGIS as well. Should I install both ...
0
votes
0answers
60 views

SQL questions testing pgRouting 2.0

Following the post:pgRouting 2.0 for Windows quick guide, cuz I have some problem with DB manager(here is the problem I posted:DB manager in QGIS 2.0), I skip DB manager and save the test shapefile, ...
2
votes
1answer
260 views

DB manager in QGIS 2.0

I followed this post:pgRouting 2.0 for Windows quick guide, and I want to use DB manager to load the test data into my PostgreSQL database. I used url to get the test data: I have set up the ...
1
vote
1answer
113 views

Problem using pgRouting 2.0

I follow this great post:pgRouting 2.0 for Windows quick guide, and I have loaded the test data from GeoJSON, like this: But I don't know how to load this layer into my database, and my DB manager ...
0
votes
1answer
189 views

Visualizing information when click in polygon‏ - Spatial join

I am new to cartodb, right now I am trying to do something similar to the cartodb tutorial: Create your first map with CartoDB.js, but with polygons using my own map. For example: I did a spatial ...
1
vote
1answer
74 views

What is the complexity of neigborhood search in PostGIS?

I have a point with coordinates x and y. I want to know all the points in a square around this point, with side L. For this, in postgresql/PostGIS, I use: SELECT * FROM table_of_points WHERE (...
4
votes
1answer
229 views

Password protecting tables in cartodb

I am trying to find an easy way to have private tables, yet exposing them to certain clients if they are authenticated. This authentication can be achieved by a token system that allows the client to ...