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

learn more… | top users | synonyms

0
votes
0answers
39 views

SRID and lat long values

I am using the following query to generate the lat long values SELECT NAME, ST_X(ST_Centroid(ST_Transform(geom, 4326))) as longitude, ST_Y(ST_Centroid(ST_Transform(geom, 4326)))as latitude from ...
1
vote
1answer
64 views

Postgis spatial join performance (point on area)

I need to update a single attribute of a large table of point data with a result of a spatial join (point on area operation) on a polygon table. I'm using the below query but wondering if this is the ...
0
votes
1answer
75 views

Seeking C++ api to read/write PostGIS?

I need read/write geometry object from PostGIS. Is there C++ library to do this?
1
vote
1answer
51 views

Complex QGIS Relations

I need QGIS to display combo boxes when editing for certain fields in a table, very similar to the functionality provided by the Relations properties in the project settings. It seems this is ...
1
vote
2answers
40 views

Saving array of objects in PostGIS field?

It is possible in PostGIS to save an array of objects in a field, and GeoServer able to read it? (timestamps) For example, a spatial table with the fields: "name", "timestamps" and "the_geom" would ...
2
votes
0answers
40 views

QGIS, connect to postgresql using windows login credentials

How to connect QGIS to PostgreSQL/PostGIS using the Windows user login credentials (Windows / Active Directory / LDAP). Is it possible? Can anyone tell me how?
0
votes
0answers
38 views

Converting polylines coordinates stored in PostGIS in column type text, in another column type geometry(MultiLineString,2154)?

I want to draw a polyline on a leaflet map and then send it to PostGis with php. I’m drawing my polylines using the Leaflet draw plugin and send the coordinates in a column “coords” in PostGIS with ...
1
vote
1answer
23 views

Getting data from postgis with hibernate is returning an hash instead of the rows

Is the first time that i use spring and hibernate starting from the basics, i need to get geometries from a postgis table populated with Qgis. I generate a persistence mapping of the table with ...
2
votes
1answer
50 views

Can I set up a PostGIS store/connection in GeoServer without user password for database?

When creating a PostGIS store in GeoServer, the guidelines I saw ask us to fill in database connection parameters including: host, port, database, schema, username, and password. This is a bit ...
1
vote
0answers
21 views

How to batch create GeoServer stores for each user?

I have unix system with a number of regular users, each with his/her own database in PostGIS. I need to create a PostGIS store for each user. I know how to do this in the web interface. Is it ...
1
vote
0answers
28 views

Request long/lat precision and request too slow [closed]

I've a PostGIS DB of 18Gb. The tab node (id, tags, geom) is very huge and I need to request long/lat on this table but it's very long when I'm using this sql request : SELECT * FROM nodes WHERE geom ...
3
votes
0answers
27 views

PostGIS - ST_SetValues is slow

I have large data in my C++ program (up to 3000x3000). I have connection to PSQL with PostGIS. Now I need to store my raster data (greyscale images) in DB raster. I am using ST_SetValues for this and ...
1
vote
1answer
60 views

Optimizing PostGIS k-nearest neighbour function for large amount of polygons?

I created a function for identifying the K nearest neighbours for every municipality, which works fine, but when I use this function for a large amount of municipalities (over 2900) it takes over 2 ...
3
votes
3answers
41 views

What is the easiest way to avoid shapefile columns being imported as PostGIS `numeric(p,s)` data type?

Considering the number of shapefiles I've imported to PostGIS (using shp2pgsql or ogr2ogr), I'm a little surprised I haven't come across this before. But I have a shapefile that has numeric columns ...
1
vote
2answers
42 views

Updating table setting a CASE with ST_Intersection

I add a new geometry column to the "mad" table so I could update it with the polygons from an Intersection with the polygons of the table "clip". Since my database is huge an it was taking a lot of ...
2
votes
0answers
27 views

WMS Layers from Azure Geoserver not loading with multiple users

I am loading multiple WMS layers into a leaflet web application from an Azure virtual machine Geoserver that pulls data from a PostGIS database on the same machine. The layers load through quickly ...
1
vote
1answer
32 views

Why can't PHP insert new geometry on PostGIS?

(Copied this question from stackoverflow, since it may have more answers here.) I'm using ogr2ogr to upload a GPX file to PostGIS. When I do it on command line (Ubuntu 14.04), it works fine. ...
1
vote
0answers
50 views

Is it necessary to use hibernate-spatial for postgis?

I'm beginner in GIS. I have a database in PostgreSQL that contains a table location, that table contains a column with type point. I have to get points and display it on google map. Once I did the ...
1
vote
1answer
27 views

Storing GML Polygon in PostGIS 2, ERROR: unknown spatial reference system

I am receiving GML as part of a larger XML message. When I receive the XML it is unmarshalled to Java objects. I have no rights to alter the schemas or the generated Java code. Therefore, I later have ...
1
vote
2answers
60 views

Calculate area parts of polygon intersections

I have a problem of calculating area sizes of intersected polygons in postgis. I would represent the problem in an example (please see the picture). I have a gridsquare table with some attributes like ...
1
vote
1answer
58 views

Finding locations within a radius of a given path [duplicate]

I am trying to figure out in Postgis the right sql statement for the following: I have in my database a table of merchants with latitudes and longitudes; I have a route from google that lists a lot ...
4
votes
4answers
352 views

Transform GeoJSON geometry to WKB

I need transform geometry from JSON like: {"type":"MultiPolygon","coordinates":[[[[-125045.48351212002,4577567.588141698],[-124816.19981552364,4577552.93014355],[-124765.99472517562,4577419.175847012]...
8
votes
3answers
197 views

Increase Tiles Caching Speed (TileStache)

I am serving vector tiles using TileStache, I have everything set up like I want. My data is stored in Postgres and I am using the VecTiles provider to serve GeoJSON tiles. I want to cache all my ...
3
votes
1answer
36 views

How to use ORDER BY GROUP BY & LIMIT

How do I use ORDER BY, GROUP BY & LIMIT in the following query through column name KPH? SELECT * FROM data WHERE ST_Within( ST_Transform(geom, 2163), ST_Buffer( ST_Transform( ...
1
vote
1answer
38 views

Area of influence with CartoDB

I am working on a city transport map, and for fun (and practice) I try to make an area of influence of transport station. But, whatever I try, something gets wrong. So two questions. 1 - How can I ...
3
votes
1answer
34 views

How to update OSM data using osm2pgsql?

I have already loaded osm data into postgresql earlier. Now I want to update the database. I have downloaded the pbf file from geofabrik. How I can update the database without deleting the existing ...
1
vote
1answer
51 views

St_ClosestPoint within St_MakeLine Query problem

I am currently trying to create the closest point (closest_ports_point_test) on the grid (lng_grid_test) from the original points (ports_point), and to create the line connecting the original and the ...
2
votes
1answer
32 views

Postgis query to sort geometries by latitude

I am looking for an efficient way to get geometries sorted by their latitude (northmost in case of polygons) when querying a postGis database Something that could work in an order by clause which ...
2
votes
2answers
63 views

Converting shapefile to postgis without giving bounding box

ogr2ogr -progress --config PG_USE_COPY YES -f PostgreSQL PG:"<myconnection>" -lco DIM=2 /path/to/myshapefilename.shp myshapefilename -overwrite -lco GEOMETRY_NAME=geom -lco FID=id -spat ...
3
votes
2answers
153 views

Transform geometry to coordinates

I need transform with postgis a geometry of type ...
0
votes
0answers
33 views

How to replace a part of OSM data in postgres?

i want to replace the apart of osm data in postgrs database. for example ,replace the osm data of china which in planet osm data. i hava used osm2pgsql, but if use osm2pgsql import the china data ,...
1
vote
0answers
15 views

like finding errors overlap on the same layer in postgis?

After meeting the overlay erroneous polygons in the same layer, I used the following query: select gid from limite_localidad where not ST_Overlaps(the_geom,the_geom) I have to leave 108 records ...
3
votes
1answer
47 views

Decoding POSTGIS dump file

I have a dump from a version of PostGIS that is no longer supported. It dumps the value of a (lat, long) as 01010000003D2CD49AE6A549409AB1683A3B19F1BF How can I decode that number (without using ...
1
vote
1answer
54 views

PostGIS - syntax error inserting GeoJSON

I am trying to insert a GeoJSON into PostGIS, but I am encountering a syntax error. I can't seem to see where the problem is - I'm sure it is an easy issue to resolve, but my inexperienced eyes can't ...
0
votes
0answers
20 views

Error on ST_UNION: SSL SYSCALL error: EOF detected

I'm using Postgres 9.4 and PostGIS 2.1. I have a table that looks like this: wkb_geometry | geometry(Polygon,4326) | id | integer | county | character varying | ...
1
vote
2answers
75 views

ST_MAKEVALID: ERROR: Geometry type (MultiPolygon) does not match column type (Polygon)

I'm using Postgres 9.4 and PostGIS 2.1. I have a table that looks like this: wkb_geometry | geometry(Polygon,4326) | id | integer | county | character varying | ...
4
votes
3answers
91 views

QGIS reduce density of points displayed

I'm working with a considerable amount of points and I would like to be able to display only part of them since they overlap a lot and are cumbersome to display. Most of them form a trajectory so ...
6
votes
1answer
109 views

Leveraging capabilities of PostGIS for large (ish) tables in PostgreSQL?

I am using PostgreSQL and PostGIS to do some exploratory analysis and eventually spatial analysis. I have a table with 40M+ points (geolocated tweets) in the United States that has a spatial index. I ...
3
votes
1answer
44 views

PostGIS: floating point representation problem with ST_AsText

I have a table with geometries, all with about 13 digits of accuracy. While trying to represent them with ST_AsText though, most vertices come out in decimal notation, but some come out in scientific ...
2
votes
1answer
22 views

Extracting the geometry of the parts not intersecting from 2 shapes overlapping each other in postgresql?

This seems simple enough, yet i do not find a clear awnser on that peculiar matter. What i am trying to do is to extract the parts of a shape that doesn't intersect with another when the 2 of them are ...
2
votes
1answer
36 views

PostGIS - default spatial reference and Z value on insert or geometry update?

I have a spatial table with a geometry column geometry(MultiPolygonZ,4326). We are trying to handle inserts and updates of geometries as GeoJSON from other systems of varying vintage. Everything is ...
0
votes
1answer
34 views

Why do I get a 503 error when pushing data to the CartoDB SQL API

I'm using ogr2ogr to push a large (200,000 feature) dataset to the CartoDB API. I'm using where_clauses to upload 500 features at a time, and sleeping every 20,000 features for a minute to give the ...
0
votes
1answer
48 views

Using pgRouting through PostgreSQL

I followed the guideline from this tutorial to use pgrouting through PostgreSQL but I can't understand the first instruction: CREATE OR REPLACE VIEW road_ext AS SELECT *, st_startpoint(the_geom), ...
0
votes
0answers
25 views

Can you merge GeoJSONs in PostGIS?

I'm not too familiar with the GeoJSON functionality in PostGIS. In essence - I'm having problems with the computation of ST_AsGeoJSON(ST_Union(g)) taking too long for large g. Is it possible to ...
0
votes
1answer
43 views

How to deal with “ERROR: GEOSUnaryUnion: TopologyException: Self-intersection”?

I'm using Postgres 9.4 and PostGIS 2.1. My table looks like this: ------------------------------------------------------------------ ogc_fid | integer | not null default ...
2
votes
1answer
48 views

For Loop Maximum Minimum Distance Between Two Lines

I have a table of line/lane ids in a table called matched_lanes, and I am trying to create a lateral query that on each id in the table to find the maximumum minimum distance between each point. I.e....
0
votes
1answer
35 views

CartoDB - inserting polygons

I am new to using PostGIS and CartoDB, and I am doing some test SQL inserts into both using PHP. I've had no problem with points, but polygons are proving to be a bit tricky - my SQL insert works ...
1
vote
0answers
31 views

pgRouting “Can't find Table”

I am using pgRouting version 2.1.0. I am using pgAdmin III. I have created a street network using Anita Graser's guide: https://anitagraser.com/2013/07/06/pgrouting-2-0-for-windows-quick-guide/ But ...
1
vote
1answer
38 views

Getting node information of a way in OSM2PGSQL data

I have a feeling this is not possible, but I am looking to get information on the nodes which are part of a line in the planet_osm_line table. The issue which I am trying to handle is I am trying to ...
0
votes
1answer
25 views

saving results of ST_offsetcurve to new table with geometry and fields from original data table

I am trying to create lines parallel to some road centerlines, using distances from the attribute table specific to each line. I'm trying to figure out how to get it to save to a new table with ...