Episode #125 of the Stack Overflow podcast is here. We talk Tilde Club and mechanical keyboards. Listen now
20

I also had to upload a shapefile to postgresql and found this solution perfect. Navigate to the given path "C:\Program Files\PostgreSQL\9.6\bin\postgisgui" Run the gui file "shp2pgsql-gui.exe"and a PostGIS Shapefile Import/Export Manager will appear. Here you can import shapefile from a specified path.


12

If you want a painless install, you might want to start over and use the OpenGeo Suite version of PostGIS. $sudo wget -qO- http://apt.opengeo.org/gpg.key | apt-key add - $sudo echo "deb http://apt.opengeo.org/ubuntu lucid main" >> /etc/apt/sources.list $sudo apt-get update $apt-cache search opengeo $sudo apt-get install opengeo-postgis Or you could ...


10

For large geometries pgAdmin will show a blank field even though there is actually data there. You can verify the existence of data by checking for null geometries: select * from "Edigeo"."GEOM" where geom_surf is NULL This tip from the PostGIS website explains in a bit more detail and includes examples of other checks you can carry out.


9

I don't believe the Shapefile loader will work in your case. However, PostGIS comes with a raster loading tool called raster2pgsql. This tool will load any GDAL supported raster format into PostGIS Raster. It is a command-line tool so to execute it you just need to run: raster2pgsql raster_options schema.table_name > output.sql So, the tool will take ...


8

I decided to go ahead and use the command line utility (shp2pgsql) to import my .shp file. Since I was able to get it working without too much trouble and a little Googling around I wanted to share this possible solution for anyone else who just wants to import a .shp file into Postgres and didn't want to deal with the hassle of trying to get pgadmin's GUI ...


7

In binary, 28356 is 0110 110 1100 0100 (hexadecimal 6E C4) In binary, 7259142 is 0110 110 1100 0100 0000 0110 (hexadecimal 6E C4 06) Therefore, it seems safe to assume that a bitwise right shift 8 will remove the SRID from the combined code, and a bitwise AND mask with 0x00FF will capture the geometry type (though casting to bit(8) seems to be easier): ...


6

I guess you are a victim of the fact that spatially enabling a PostgreSQL db with PostGIS creates a bunch of functions in the "public" namespace. If you backup the public namespace and then restore it to a PostGIS db these functions will already exist, causing errors. As Paul Ramsey wrote (in a blog post explaining the issue in detail and giving advice): ...


6

If you are working on your workstation it's more a matter of taste. Knowing how to use psql is useful for some situations like running sql scripts from files, pipe it with other tools, etc. It depends on your needs. My everyday work is done using pgAdmin and I only go down to the CLI when needed. On the other hand psql is sometimes your only option when ...


5

I don't think the issue is with the ST_GeomFromGeoJSON call. It's possibly the id field you have OR your table structure. Can you provide your table structure. SELECT ST_GeomFromGeoJSON('{"type":"Point","coordinates":[-48.23456,20.12345]}'); alone works fine, so doubt that is your issue. If the above query fails, then your postgis install is corrupt in ...


5

My problem was that I could not find the pg_dump command. I thought it was a subcommand of psql command. You need to add c:\program files\postgresql\9.3\bin> to path(system variables) of windows or use its direct executable file: export: c:\program files\postgresql\9.3\bin> pg_dump.exe -U postgres test2 >"d:\backup.sql" import: c:\program files\...


5

To load shapefile to postgres database Go to start menu Search "POSTGIS 2.0 Shapefile and DBF Loader Exporter" and load your shapefile through it. OR You can load your shapefile to QGIS. Connect to your database from within QGIS. Then use Database manager to import your shapefile to your desired postgres database.


4

I can't believe I'm having the same issue in 2014 with 12.04. Neither the GUI nor command-line shapeloader install using the opengeo-suite. I simply followed the noob-friendly directions on this website: http://www.staygeo.com/2013/05/enabling-postgis-shapefile-and-dbf.html, which parallel the instructions by RK almost 2 years ago. "Install opengeo-...


4

The easiest would be to setup PostgreSQL user group roles and assign permissions based on those roles. It's a lot easier in PostgreSQL 9.0+ since you can use DEFAULT PRIVILEGES. --this will take care of future tables in a database ALTER DEFAULT PRIVILEGES GRANT ALL ON TABLES TO gisadmins; -- this will take care of existing tables in public schema. As I ...


4

PgAdmin has limits on the largest object it can display in a table cells. Large geometries frequently exceed this limit, which results in an "empty" cell, confusing to new users. If you call ST_NPoint(geom) or ST_GeometryType(geom) you can see that the geometry is in fact there, and does have data in it, you just cannot see it in a PgAdmin cell.


4

Here is a...unique(??) way of doing it. I managed to create routes using QGIS 2.14 with GRASS and rasters. I got the idea from here. To summarize we will rasterize the land and sea, run a proximity algorithim, invert the output, run a cost, and drain algorithm in GRASS and finally polygonize the routes to polylines. 1) Rasterize your land and sea. First ...


4

You seem to have a PostGIS installation that was created by loading the postgis.sql file, rather than using the CREATE EXTENSION postgis command. So when you dumped your database, you got not only the data, but also all the function definitions, which includes references to the 2.1 PostGIS library. Install PostGIS 2.1.8 on your new system. Create a blank ...


3

The pair that you want is PG_Dump and PG_Restore... I use PG_Dump on a scheduled task to backup our PostGIS databases in a format that can be restored easily - and more importantly will work! pg_dump.exe --file=c:\Your\path\BACKUP_Name.Backup -Fc -Z9 -o DatabaseName Custom format Z9 compression (to save space) Include objectIDs and then to restore on the ...


3

Any table in the instance can be registered with sde. But sde is what has to do that registration. I suggest starting with this Essential Reading for Geodatabases Registering with the database


3

Yes, absolutely. PostgreSQL is set up to only accept local connections, and GeoServer is set up to use the loopback address i.e. 127.0.0.1 on port 8080. So you can run the standard installers and everything should just work when you type: http://localhost:8080 You'll need to set up GeoServer to see your PostgreSQL database, but that's standard stuff ...


3

This response may be late,but i had the same trouble and i found the solution. Try this command on your terminal : $sudo ln -s /usr/lib/postgresql/9.1/bin/shp2pgsql /usr/bin/shp2pgsql


3

You can also use this single command which helps in looping much easier and also does not need to create .sql separately, for f in *.shp do shp2pgsql -c -D -s 4326 -I $f public.${f%.*} | psql -h hostname -d dbname -U usrname done


3

Here is a code sample to show how you could publish a WMS layer hosted on geoserver in Leaflet: var map = L.map('map').setView([51.505, -0.09], 8); var forest2000 = L.tileLayer.wms("http://138.26.24.xxx:8080/geoserver/tiger/wms",{ layers: 'forest2000', format: 'image/png', transparent: true, opacity: 0.7 }).addTo(map); Change ...


3

Your .csv file is mis-formatted. From the documentation: Note: Many programs produce strange and occasionally perverse CSV files, so the file format is more a convention than a standard. Thus you might encounter some files that cannot be imported using this mechanism, and COPY might produce files that other programs cannot process. I don't know where ...


3

Solved, thanks. Needed to being ST_Transform instead of ST_SetSRID (see last comment / link) ALTER TABLE public.roads_sco ALTER COLUMN geom TYPE geometry(MULTILINESTRING, 27700) USING ST_Transform(ST_SetSRID(geom,4326),27700)


3

In general to update one table from another, and this applies whether there is a spatial join condition or not, you follow the UPDATE...SET...FROM...WHERE pattern, see the docs. In this case, your WHERE condition is an ST_Contains or ST_Intersects, but it is essentially the same as joining on two IDs or some other condition. It is often convenient to use ...


3

The error happened because EPSG:5514 is no added in spatal_ref_sys table. So you can add it using the following query. INSERT into spatial_ref_sys (srid, auth_name, auth_srid, proj4text, srtext) values ( 5514, 'EPSG', 5514, '+proj=krovak +lat_0=49.5 +lon_0=24.83333333333333 +alpha=30.28813972222222 +k=0.9999 +x_0=0 +y_0=0 +ellps=bessel +towgs84=589,76,480,...


3

PostGIS should work for your task but: Reproject your data into some Cartesian coordinate system (like Web Mercator) because computations on the WGS84 ellipsoid (geographic coordinates) in PostGIS take longer time. Also for geography ST_Buffer may not behave as expected if object is sufficiently large that it falls between two UTM zones or crosses the ...


3

Yes, but you would invite trouble doing it. If the external drive is missing then PostgreSQL will fail to start, and be extremely difficult to repair. http://thebuild.com/blog/2013/03/10/you-cannot-recover-from-the-loss-of-a-tablespace/


2

You might also want to look at SPIT, which is a PostGIS loader plugin for QGIS


2

No and yes. You can configure pgadmin to call external programs, like shp2pgsql-gui, out of a menu option, which can make things easier for new users (see OpenGeo Suite, for example). But a full commandline like shp2pgsql has too many parameters needed to be effectively called by pgadmin.


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