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

learn more… | top users | synonyms

3
votes
3answers
40 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
1answer
36 views

Updating table setting a case with ST_Intersection

I'm trying to update a geometry column using this command: UPDATE mad SET geom_p=CASE WHEN ST_CoveredBy(mad.geom,b.geom) THEN mad.geom ELSE ST_Multi(ST_Intersection(mad.geom,b.geom)) END AS ...
2
votes
0answers
23 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
27 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
36 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
23 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
54 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
35 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 ...
3
votes
4answers
319 views

Transform GeoJSON geometry to WKB

I need transform geometry from JSON like: ...
8
votes
3answers
178 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
35 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
36 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
31 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
42 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
30 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
57 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
151 views

Transform geometry to coordinates

I need transform with postgis a geometry of type ...
0
votes
0answers
28 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
43 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
47 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
17 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
35 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
82 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
107 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
43 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
28 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
32 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
0answers
37 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
21 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
29 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
39 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. ...
0
votes
1answer
30 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
27 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
37 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
23 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 ...
1
vote
1answer
20 views

Geoserver getfeatureinfo returns big number with exponential format

I have a Geoserver layer, which contains numbers on a million scale in one of the attributes. If I query these features, I get back a number in this format: 4.1E7 instead of this: 410000000 Is there ...
1
vote
2answers
25 views

Assigning variables into a API text string postgis

I am trying to assign values to variable in PostGIS. I have a API similar to: example I want to create a trigger that parse the x,y coordinates from my geometry column to a field containing the ...
2
votes
1answer
35 views

Export to DXF from PostgreSQL using ogr2ogr

I have a large dataset in a PostGIS database (OS MasterMap). My aim is to export specific areas of this dataset to DXF, while retaining the attributes of the data as layers in the DXF. My current ...
1
vote
0answers
19 views

How to add large file (in GB) in Xcode project?

I am working with Mapbox Framework. I have generated MBTiles file which I want to use in my project (for iPhones and iPads) but that file is having size of 3GB (which I can't use in project). When I ...
2
votes
3answers
64 views

Checking if a polygon contains multiple point geometries using PostGIS?

This is not a a duplicate of the "Count Points in Polygon with Postgis", since in the ST_Contains(geometry geomA, geometry geomB), this case has two geometries (start_geom and end_geom) in geomB, ...
0
votes
2answers
51 views

Casting between PostGIS types in GeoAlchemy

I'm working with OpenStreetMap data, and loading it into a PostGIS database using osm2pgsql. The issue that I am currently dealing with is that osm2pgsql stores the shape as a geometry type. I'm ...
1
vote
0answers
28 views

Python GDAL open dataset in PostGIS with quotations

Error trying to use gdal to open a dataset in PostGIS with the following code: ds = gdal.Open("PG: dbname=nyc host=localhost user=postgres password=mypassword port=5432 mode=2 schema=public ...
1
vote
0answers
24 views

PostGIS Svg Projection on Google Map

Context I have a database with spatial data such as areas and points. Some of the areas in my db are coming from OpenStreetMaps, representing some regions. (Which are kinda small at earth's scale, ...
0
votes
2answers
31 views

Building efficient multi geometry type database with PostGIS?

I'm trying to build a PostGIS database which must be able to store (multi)points, (multi)linestrings and (multi)polygons. The goal is to efficiently querying the data with ST_Intersects and other ...
2
votes
1answer
38 views

ST_ClosestPoint equal, but not touching

I have two polygons where one should cover the other. They were created in qgis using snapping, however when I checked their topology with postgis it turned out one point on the inner polygon ...
1
vote
1answer
35 views

Find the convex hull around a set of polygons?

I'm using Postgres 9.4 with PostGIS 2.1. I have a table with the following columns: Column | Type --------------+------------------------ ogc_fid | integer ...
0
votes
2answers
30 views

shp2pgsql: how to know the schema.table?

I want to import a shapefile using shp2pgsql. I am using Mac with Postgis 2.2. Based on this reference: A conversion and upload can be done all in one step using a UNIX pipe: shp2pgsql -s ...
2
votes
1answer
40 views

“column does not exist” for SELECT ST_Point

I am using psql (9.5.2) and postgis 2.2.2. In the psql shell, I have created a spatial database and connected to it. I want to insert a point into the postgis geodatabase. To do so, I first create a ...