Tag Info

Hot answers tagged

8

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 ...


7

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 ...


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

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 ...


6

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 ...


6

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 ...


6

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. ...


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

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


5

Yes, you can, and the PostGIS documentation page for ST_Distance_Sphere and ST_Distance_Sphereoid both contain good examples. Here's one with lon/lat values plugged in to the ST_Point function: SELECT st_distance_sphere( st_point(-69.23, 44.61), st_point(-75.42, 43.55) ) The result in this example is 508166.687378974 meters. To ...


5

In ArcGIS you don't need to create a buffer layer as AllenT suggested. You can do it just by using the selection tools without creating any additional layers. Firstly, you will need a layer representing your coastline. Depending on where your area of interest is, you may need to either digitise it or download it. Then, you can use Select by Location to ...


5

PostgresSQL/PostGIS is a relational database management system ( RDBMS) and the original order of the columns have no significance. If you want to change the order in which they appear, use SQL: select Field2, Field3, Field1 from table; select Field1, Field3, Field2 from table; And if you want to keep this ordered result as a table in the database, use ...


5

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$$; ...


5

You no longer need to use the templates or even the script in the new versions of Postgis. As you can see in the Postgis - installation page, all you need is run the CREATE EXTENTION command. -- Enable PostGIS (includes raster) CREATE EXTENSION postgis; -- Enable Topology CREATE EXTENSION postgis_topology; -- fuzzy matching needed for Tiger CREATE EXTENSION ...


4

I am a maintainer at Application:Geo. I will restore postgis 1.5.3 binaries in the next few hours. If you are in a hurry check http://download.opensuse.org/repositories/home:/tzotsos:/postgis/openSUSE_12.1/ where the old binary is posted now. Thanks for the report, Angelos


4

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 ...


4

I've updated the instructions on the website. Hope it's a bit clearer. Nicklas is right you don't really need the makepostgis.bat if you just copy the like folders to your postgresql install (you could also remark out the create db in it since it does copy all the files). You should be able to just overlay ontop since the zip structure is the same as ...


4

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 ...


4

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. ...


4

About different geometry types: From your description it looks like you should absolutely store your trajectories as linestrings. If you store them as points or multipoints you will have to build linestrings in runtime if you don't only want to do the calculations on the points defining the trajectories but also what is between the points. an Example (in ...


4

Something even simpler: -- I just create a dumb table: CREATE TABLE point ( id serial primary key, anything varchar ); -- Add field names with default values as desired: ALTER TABLE point ADD COLUMN username varchar DEFAULT "current_user"(); ALTER TABLE point ADD COLUMN address varchar DEFAULT "inet_client_addr"(); ALTER TABLE point ADD COLUMN ...


4

PostGIS Windows dl site provides information about running it without installing. The blog it links to has all the info needed.


4

Try flipping you axis order, since "-121.7532461" is not a valid latitude. It should be in the range -90 to 90. If your string is already hard-coded, use ST_FlipCoordinates to reverse the axis order. SELECT regions.name FROM regions WHERE ST_Intersects(ST_FlipCoordinates(ST_PointFromText('POINT(38.53872 -121.7532461)', 4269)), ...


4

You can find the source code of osm2pgsql here: https://github.com/openstreetmap/osm2pgsql/blob/master/reprojection.c and you will find this note in line 67: /* hard-code the source projection to be lat/lon, since OSM XML always * has coordinates in degrees. */ pj_source = pj_init_plus("+proj=longlat +ellps=WGS84 +datum=WGS84 +no_defs"); /* ...


4

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 ...


4

If you want to make browser download the generated KML, an extra header is needed: header("Content-Disposition: attachment; filename=MY_KML_FILENAME.kml"); Otherwise, just ignore the message, it's harmless. Chrome doesn't recognize the KML-specific Content-Type, and expects ordinary text/xml or application/xml, but since it's all XML, it should be parsed ...


4

Since your search region is circular, it is perhaps best not to consider it to be a polygon, but as a point with a radius and to use the ST_DWithin() function: ST_DWithin — Returns true if the geometries are within the specified distance of one another. It should save you and the processor a lot of effort.


4

ST_AsLatLonText documentation says: It is assumed the point is in a lat/lon projection. The X (lon) and Y (lat) coordinates are normalized in the output to the "normal" range (-180 to +180 for lon, -90 to +90 for lat). So you are seeing your point (in 900913) "wrapped" around until it eventually gets into the normal range. To see your ...


4

Aha, I was confused for a while, but now I'm not. Generally, I would expect a buffer in UTM to match up pretty well to a geodetic buffer. Close enough you wouldn't be able to see the difference. But, looking at your map, it looks like you're working in a European city, but you're generating your PostGIS buffer in UTM 18, which is valid in the area of New ...


4

What do you mean by line = length? I guess what you mean is that your subquery will find the id of the shortest line. Then you ask in the outer query for a line with that id and some number less than 500. If the shortest line then have a a number that is more than 500 you will get nothing back. EDIT Something like this should work if I understand you ...



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