Hot answers tagged

21

I just had the same problem on Ubuntu Server 14.04. I installed postgis extension from the official Ubuntu repositories using apt-get install postgis. Then, find /usr -name postgis.control didn't return any results. The reason was extension/postgis.control wasn't installed because postgis-scripts wasn't. $ aptitude search postgis i libpostgis-java ...


15

I think this is the most frequent question on PostGIS list over time :-) If your data is in SRID 4326 and you use geometry type the result will not give any meaning. It is in degrees. To get the result in meters just cast to geography type and ST_Distance will calculate the distance along the great circle instead and return in meters. geometry(Point,4326)...


11

It's necessary to calculate the actual intersection, e.g. http://postgis.net/docs/ST_Intersection.html SELECT ST_Length(ST_Intersection(line,polygon))


11

In Ubuntu 14.04 you also need to install the postgresql-9.3-postgis-scripts package. After I ran sudo apt-get install postgis postgresql-9.3-postgis-scripts I was then able to successfully run CREATE EXTENSION postgis; in my database to initialise PostGIS.


11

I would recommend using pg_dump and pg_restore which work very well in exporting data from one database and then restoring it to another database. there is a nice tutorial here http://www.mkyong.com/database/backup-restore-database-in-postgresql-pg_dumppg_restore/ But simply you will want to export using a command like pg_dump -U username databasename >...


10

I'm surprised it's quite so coarse, but there it is. It's not DISTINCT, per se, it's the '=' operator, which is defined for geometry as 'equality of the index keys' which means practically 'equality of the 32-bit bounding boxes'. You can see the same effect just using '=' directly, select 'POINT (0.000000001 0.000000001)'::geometry = 'POINT (0.000000001 0....


10

Be aware that the GeoJSON specification states that The GeoJSON object must have a member with the name "type". This member's value is a string that determines the type of the GeoJSON object. http://geojson.org/geojson-spec.html#geojson-objects If you decide that you do not want/need to be compliant with the GeoJSON spec, you can use the - (minus) ...


8

Celenius, If you don't use the -t option with size dimensions, then your raster file will come in as a single record. I just noticed an error in the docs which is probably what's confusing you. I'll fix that. The -t should always be followed by a widthxheight. If you want it to be chunked say in 100x100 pixel width height -- as Mapperz says -- use the -...


8

When you spatially-enable a PostGIS database, the relevant functions, SRS table, and views are placed in the public schema, as you state. That does not mean that all or any of your own spatial tables need to be in the same public schema. PostGIS will still work on all spatial data in the "new" schemas. In fact, I usually place my application-specific tables ...


8

Internally, ST_Buffer(geography, ...) uses a fixed projection guess with _ST_BestSRID, which are typically UTM zones or whatever makes sense to the algorithm. This is why you see the differences, because they are different projections that are not optimized for the location of the points. For simple point buffers, you could use a custom azimuthal ...


7

Maybe something like this (I'll assume you have some primary key column "id" in each table): SELECT A.id, A.code AS Code, A.sign AS Sign, B.id, ST_Distance(A.geom, B.geom) AS Distance FROM Table_A AS A, Table_B AS B WHERE A.id IN ( SELECT X.id FROM TableA as X, TableB as Y -- Here's the important part: refer to the A table **outside** of the ...


7

You can use a typmod to specify the srid in the geometry_columns view, something like CREATE VIEW pippo AS SELECT st_geometryn(shape,1)::geometry(Geometry, 4326) as geom FROM events The manual has more info.


7

Simple answer: Don't. You shouldn't do it that way. From the OSM road Shapefiles, it is impossible to distinguish between intersections and over/underpasses. You'll create intersections that don't exist in reality if you split all seemingly crossing roads. You'll need to get your hands dirty with the original OSM file, if you don't want to use existing ...


7

GeoDjango 1.4 supports PostGIS 2.0 with too many workarounds to make it worth it. Instead, install GeoDjango 1.5 beta which natively supports PostGIS 2.0 and switch to the official release next month.


7

To my knowledge, GIS as Arcmap or QGIS can't support layers with several primitives (point, polygon or polyline). What GIS do you use? You can try to create tables for each type: Point: SELECT * INTO newtablepoints FROM initial_table WHERE ST_GeometryType(geom) = 'ST_Point' ; polygon : SELECT * INTO newtablepolygones FROM initial_table WHERE ...


7

What does the “service” box in the PostGIS connection dialog stand for?: The service entry allows you to specify a service file that contains the connection parameters needed to access a certain postgresql database. By having a service file, you can then ignore having to enter other details like database, host, port, user and password. Here are some of ...


7

To create 1 random point for each polgyon in a table I have used this table and code: DO $$ DECLARE r RECORD; BEGIN FOR r IN SELECT id_0 FROM "Grid" LOOP -- RAISE NOTICE 'affected row id: %', r.id_0; UPDATE "Grid" SET "point_geom" = (SELECT RandomPointsInPolygon(geom, 1) FROM "Grid" WHERE "id_0" = r.id_0) WHERE "id_0" = r.id_0; END LOOP; END$$; ...


7

Assuming your data table look like: streetid housenumber 100 12 100 15 101 12 101 18 You could do a basic SQL query: SELECT streetid, count(*) FROM tablename GROUP BY streetid Where this might get more GIS-y and interesting is the case where streets with the same ID are not contiguous. You didn't mention if this is a ...


6

Revert your software to 1.5, then follow the HARD UPGRADE instructions in the manual, doing the dump step, then the software upgrade, then the restore steps. http://postgis.net/docs/manual-1.5/ch02.html#hard_upgrade


6

You're constructing a POINT with four ordinates. To the parser that looks like a POINT ZM. You want to construct a LINESTRING. ST_SetSRID(ST_MakeLine(ST_MakePoint(lon1, lat1), ST_MakePoint(lon2, lat2)), 4326)


6

For those who will stumble on this question like me.. It appears that the plugin has been generated (https://github.com/strk/mapnik/tree/2.3.x-pgraster) and has been merged in official Mapnik Repo. The branch is 2.3.x (https://github.com/mapnik/mapnik/tree/2.3.x) Now you can build Mapnik from the branch and use PGRaster plugin to use Raster data from ...


6

you should check out Store, manipulate and analyze raster data within the PostgreSQL/PostGIS spatial database document by Pierre Racine and Steve Cumming which was presented at FOSS, here. There are lots of function defined as raster statistics to solve your problem. i think ST_SummaryStats will help you about zonal statics, of course, not enough. ...


6

You are looking for PostGIS 2.0 Shapefile and DBF Loader Exporter, so access "C:\Program Files\PostgreSQL\9.3\bin\postgisgui\shp2pgsql-gui.exe"


6

I made and tested the functions I need. For point generation I used a simplified version of the functions in trac.osgeo.org/postgis/wiki/UserWikiRandomPoint: CREATE OR REPLACE FUNCTION random_point( x_min integer DEFAULT 13, y_min integer DEFAULT 49, x_max integer DEFAULT 16, y_max integer DEFAULT 51, srid integer DEFAULT 4326 ) RETURNS geometry AS $...


6

Your table seems to be an Amersfoort oblique stereographic (EPSG:28992). To use a lon/lat point as input, you need to create it as a lon/lat point, then transform it, so your distance function would look something like this: ST_Distance(geom, ST_Transform(ST_SetSRID(ST_MakePoint(5.112472, 52.008417),4326),28992))


6

If you only want Polygons or Multipolygons from ST_MakeValid you can use ST_Dump to extract the constituent geometries and then test for the geometry type. ST_MakeValid will sometimes produce Points or LineStrings which is where the GeometryCollection is coming from. Try something like: SELECT g.geom, row_number() over() AS gid, FROM (SELECT ...


6

Your coordinates are out of order. If you reverse the order of coordinates in the first query, postgis says: 708.55982691. In postgis it's lon, lat, not lat, lon.


5

Naturally you back up your database on a regular basis, so it should be a simple case of restoring from the last backup. PostgreSQL also has cool features that allow live swapping of a production database using the Write Ahead Logging system and some very clever thinking. That said, if for some reason your backups aren't up to date, or gasp non existent, ...


5

SRID? -s 4236 (required) Are the arguments -I -C -M all optional? -C Apply raster constraints -- srid, pixelsize etc. to ensure raster is properly registered in raster_columns view. (required) -M (Vacuum analyze the raster table.) optional -I (Create a GiST index on the raster column. ) optional but recommended -t TILE_SIZE (Cut raster into tiles to be ...


5

I found a tutorial on intersecting vector polygons with a large raster coverage using PostGIS WKT Raster. Might have the answers you're looking for. The tutorial used two datasets, a point shape file that was buffered to produce polygons and a series of 13 SRTM elevation rasters. There was a lot of steps in between but the query used to intersect the ...



Only top voted, non community-wiki answers of a minimum length are eligible