PostgreSQL is an open source object-relational database system.

learn more… | top users | synonyms (1)

4
votes
2answers
625 views

PostgreSQL, PostGIS and GeoServer publishing 1.7 million records

I have a polygon geodata in postgresql and I am publishing out to geoserver the 1.7 million records. How long is too long when processing? EDIT: Forgot to mention that I successfully published a ...
4
votes
3answers
2k views

How to join two tables using date, time, lat and lon as common field?

I am sorry if the question is elementary but I did not find any clear discussion or guide to understand how to run a query in postgresql. So that a pointer to another website would be also useful. ...
2
votes
0answers
117 views

Pgrouting can't find postgresql (on Mac)

I've installed both postgreSQL 9.0 and postgreSQL 9.1 on my mac, using binaries from http://www.kyngchaos.com. I also have postgis 1.5 installed. I tried to install pgrouting, again using binaries ...
3
votes
1answer
250 views

PostGIS Trigger doesn't work

The trigger below don't works and I need that when a new pipeline (adutoras) is inserted or updated, the procedure check what are the municipalities (municipios) that intersects the pipes and put the ...
1
vote
1answer
161 views

Ordnance Survey TOIDS: converting from strings to integers

I'm planning to load a number of OS datasets into Postgres/PostGIS. In order to save space and speed up queries, I'd like to strip the 'osgb' off the front of the TOID strings and then store the ...
1
vote
0answers
69 views

how to group interconnected edges inside a table?

Given a table and a random set of edges with source and target columns, how can I find out which edges connect with each other and then group them together using PostgreSQL?
2
votes
3answers
561 views

ST_Covers: Geography Implementation is not working, Geometry Implementation taking too long

I have a query: (This query uses geographical implementation of ST_Covers function) SELECT ST_Covers(ST_GeoGraphyFromText('MULTIPOLYGON(((179 -89,179 89,-179 89,-179 -89,179 ...
1
vote
0answers
48 views

given sets of vehicle passing on each edge, how to get most used vehicle to avoid transition?

I have two tables: one, the resulting table from converting osm.xml via osm2po so I can use pgRouting, and another one that contains information on what edges each public vehicle passes through ...
1
vote
1answer
297 views

pgrouting shortest path result in wrong order

I am using the Dijkstra shortest path algorithm to plot a route between two points using the latest version of pgrouting in postgresql 9.1 with postGIS 2.0.1. I am able to get a correct result ...
4
votes
1answer
605 views

Transit Routing using pgrouting as core routing engine?

i'm a student and i'm working on a project. the aim is given two sets of coordinates(origin & destination) route through the map to determine the optimum path using public transportation and of ...
3
votes
2answers
928 views

Can I import a .gml file into Spatialite in QGIS?

I have some Ordnance Survey Address Base data files which have a .gml extension. I have imported them to GGIS as vector layers along with some mapping data but would like to explore them with ...
5
votes
2answers
2k views

PostgreSql query problem with aggregate functions

I need help with a PostgreSql query. I need to find all parcels which have bigger area then average. I'm using this query: SELECT OBJECTID FROM parcela WHERE ST_Area(geom)<(SELECT ...
0
votes
1answer
674 views

Failed to delete a domain from the database

I am trying to delete a domain I am using from ArcCatalog -> Feature Class Properties-> Subtypes-> Domains. But when I try to do that I get this error message: "Failed to delete a domain from the ...
3
votes
0answers
574 views

What means the gdal_translate message: “Too many command options”? [closed]

I have a little question, regarding the gdal_translate-utility: I want to extract raster-files from database-stored gridfiles using the gdal_translate: gdal_translate -ot Float32 -of SAGA PG: ...
1
vote
1answer
85 views

PostgreSQL query selection in MapServer is not working?

When I make a selection in the query in Mapserver I got a blank page. The query I use is: DATA "geom from (select * from bagmutlijst_201210.vo_gepland_gemeente where gemcode = '0141') as selection ...
1
vote
2answers
307 views

Is it possble to set a PostgreSQL schema and table name as a variable in Mapserver?

In Mapserver it is possible to set a variable when connecting to postGIS. This way I have created an fill-in form where you can enter a postcode and then you get a result. How can do this with ...
0
votes
0answers
58 views

How to format spreadsheet to input point data for later import to PostgreSQL

I want to create a spreadsheet to hold data from our cemetery. I will use the spreadsheet to get the data into a PostgreSQL database. What I dont know yet is the format that the point (positional) ...
3
votes
3answers
736 views

Geoserver, PostGIS and Postgres setup better on which web server?

Is it better to have Jetty or Apache as a Webserver for GIS stack?
8
votes
3answers
376 views

Why doesn't pgRouting return the best path?

Let the next portion of a graph: When I use the function shortest_path between the points A and B, I got the blue path. Why does this happen?
2
votes
1answer
700 views

Update query does not affect any rows, privileges are set to public and allow all actions

I've been trying to update a column of integers called level_dept in a table called adminboundaries within the sql query tool on pgadmin3 and the query runs and finishes with a lot of lag, but 0 rows ...
5
votes
2answers
209 views

How can I join a spreadsheet to a point layer?

I have a layer with point data for the grave positions in our cemetery. We are gathering data from each headstone. If I enter the data to a spreadsheet can I later associate the spreadsheet with the ...
4
votes
1answer
387 views

postgis create geom from lat long

I ran this query successfully, but the column highwayid isn't updated as expected: update unique_latlongs set highwayid = (SELECT h.osmid FROM rn.highways h WHERE h.geom && ...
-2
votes
1answer
397 views

How do I set the protocolVersion using the jdbc postgres driver? [closed]

I attempted to concatenate a string to the url parameter of the below in order to set the protocolVersion, but I'm not sure how to keep the string in the url without altering the name of the database, ...
2
votes
3answers
423 views

how can i use the postgresql's Geometric Types

How can i implement Geometric Types in postgresql db.. Postgresql db is hosted in windows os.. and how can i get whether point is in circle or not? getting errors when i am using predefined ...
0
votes
2answers
3k views

slow import via osm2pgsql to postgresql database

I try to import berlin.osm (33Mb) into the postgres database 9.1 via osm2pgsql. My desktop is mem 2Gb,proc 2Ghz,hdd 60Gb, RHEL6u2x64. This process is so slowly in particular when I'm going over ...
7
votes
2answers
1k views

vehicle tracking system in postgresql performance

I am developing the vehicle tracking system using postgresql. When the data comes from device to my DB server, I am calling the following functions after inserting data into log table. Distance ...
12
votes
2answers
3k views

Postgres/Postgis spatial index - no speed up

I have a spatial table in a postgres/postgis database. Every row in it represents a Polygon. It is of following form: +----+--------+ |gid | way | +----+--------+ |241 | 01030..| The geometric ...
1
vote
1answer
539 views

Broken image from mapserver, png not rendering

I'm trying to render WMS MapServer data on top of Google Maps using the OpenLayers JS library. My problem arises when I try to render the data as a series of points throughout the New York City area. ...
0
votes
1answer
478 views

Column exists in DatabaseMetaData, but does not exist in relation?

I am using JDBC to query a PostgreSQL database. And I want to populate a column called wiki_url based on the value of another column, level which will determine which value of 5 columns to concatenate ...
2
votes
2answers
967 views

How to project a geoJSON file in OpenLayers?

I have a json file well formed. I want to read a json file as a base layer from OpenLayers. <script type="text/javascript"> var styles = new OpenLayers.StyleMap({ ...
2
votes
3answers
406 views

Creating feature dataset by pgAdmin (PostgreSQL) that can be useD in ArcSDE connection?

How to create feature dataset in PostgreSQL (pgAdmin) that can by use in ArcSDE conection (ArcGIS)?
1
vote
1answer
267 views

Convert native box to geometry

I have an old table that contains bounding boxes of lat long coordinates in a "box" type. How do I convert them to PostGIS geometry ? I tried using ...
3
votes
1answer
2k views

Merging multipolygon data into a polygon

I have a MultiPolygon representation of some shapes in POSTGIS, I am trying to convert them to Polygon data. I tried, st_geometryn(r.geom, 1) but that didnt work because the way polygons are ...
0
votes
1answer
488 views

Eclipse can't find Driver.class from postgresql-9.2-1002.jdbc4.jar file nor AbstractJdbc4Connection class

I am using the jar file in my configuration path after following the example from postgresql/jdbc tutorial. But after I added the jar file and configured it for my reference library when I run it I am ...
2
votes
0answers
193 views

Projecting PostGIS in Openlayers

I have a layer(base layer) with MultiLineString Geometries and now I need to project the layers(overlays) in the base layer from PostGIS using OpenLayers. However, I got an idea how to project table ...
2
votes
1answer
303 views

How to filter or query data in GRASS?

I have a PostGIS dataset with just over 2 million rows. Each row has a PostGIS geometry point value and several attributes. I'd like to do some analysis on certain subsets of the data, but I can't ...
6
votes
2answers
3k views

PostGIS ERROR: could not load library “/usr/lib/postgresql91/lib64/postgis-2.0.so”

OS is openSUSE 12.1, PostgreSQL - 9.1, PostGIS - 2.0. Postgre/PostGIS are installed from Application:Geo repository. After recent updates when I try to connect to my database I get the following ...
6
votes
3answers
295 views

Are there any open source Python modules compatible with arcpy that will allow complex SQL queries?

I am trying to handle string (non-geometry) duplicates in a large attribute table of a shapefile without having to convert to gdb and back or connect to a postgres db. I need to be able to use a count ...
3
votes
1answer
744 views

How to manage the osmosis command line?

This refer to this post : Read Here I'm in trouble when using osmosis command line to export data from planet-latest.osm.bz2 directly to osm2pgsql using some criteria to select what to transfer. I ...
1
vote
0answers
523 views

How do I find the pgAdmin connection parameters?

I need to create a connection from arcmap to pgadmin using a python module because of an arcpy script, but I am unsure of how to get the pgadmin connection parameters. I think it may be some kind of ...
3
votes
1answer
187 views

Creating a vector based design from a raster database

I have a raster table that I've created using Postgis raster. It has 365 records and the table looks like this: +-----+------------+---------+------------+ | rid | rast |filename |date_of data| ...
1
vote
1answer
2k views

SRID 4326 and geometry conversion

From this line: SELECT ST_AsEWKT(geom) FROM buildings LIMIT 1; I get this result: "SRID=4326;MULTIPOLYGON(((404269.308014269 4543087.6184561,...)))" I'm wondering if the coordinations are ...
2
votes
1answer
4k views

How to fix incorrect SRID of the geometry column? [duplicate]

I've been told to use AddGeometryColumn to properly add a column with correct/preferred SRID. however I wonder if it's possible to fix/change the current column instead of adding a new? If there's no ...
1
vote
1answer
231 views

Why does Find_SRID return zero?

using SELECT Find_SRID('public', 'myTable', 'myColumn'); I get zero as the result, what does it mean? Regards,
1
vote
1answer
362 views

loading multiple shapefiles using shp2pgsql in a bash script

I have this bash script (loadShapefiles.sh) to load multiple shapefiles at a time. The script and the shapefiles are stored in the same directory. When I run the bash file with the command of bash ...
0
votes
0answers
161 views

Can I do full SQL expressions through the “WHERE” clause in UpdateCursor?

Run as a standalone python script for a shapefile in a toolbox on ArcMap. For example: psql_url_pop = '''SELECT shapefile.NAME_# + shapefile.WIKI_URL FROM public.shapefile''' rows = ...
1
vote
2answers
783 views

dealing with 2 different coordination systems in postGIS

I'm seeking a function that converts degrees to the following format: POINT(405115.926025391 4543389.4354248) I dont know this format's name, but is there any function to do so? Cheers, EDIT ...
2
votes
1answer
776 views

Getting points within a range from a postgres database?

ok so this is query i'm making which returns no result: SELECT building.gid FROM building WHERE ST_Dwithin(building.geom, ST_GeomFromText('POINT(long lat)',4326), 400) If I run this ...
3
votes
3answers
2k views

Spatial index to speed up the query

I have spatial table in PostgreSQL 9.2 / postgis 2.0 that I have to extract some information from it. But my query is hopelessly slow. How could I make use of spatial index to improve the performance ...
5
votes
4answers
2k views

What is the easiest way to detect geometry type from text in PostGIS?

I have some records with different types of 'geometries (poly,line,points)' stored as string. I'm trying to use ST_GeomFromText as I want to do some intersection with them. What is the easiest way to ...