Tag Info

Hot answers tagged

25

If you have a Windows computer, you can use good 'ol CMD.EXE with a few esoteric for-loops. Make sure you do this in a "contained" directory with only the shp/sql files that you need to load. First step, create the SQL loader files (I also assumed you have Lat/Long WGS84 data with 4326 .. update this to your SRS): for %f in (*shp) do shp2pgsql -s 4326 %f ...


18

If want to stick to a GUI then the newer version of pgAdmin has Shapefile Loader that can be used as a bulk load


7

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


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

While I have not actually installed it, I had read about the pgAdmin plugin called "PostGIS viewer" (Windows only) referenced here (2010). The first request to add something like this (that I found) was ticket #485. Germán Carrillo developed a multiplatform "PostGIS viewer" based on PyQGIS. You can access old versions here, here, and here, all from 2011. ...


4

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


3

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


3

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.


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

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

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


2

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


2

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


2

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


2

I just figured out that one of my earlier approaches would have worked, but I had a typo in my postgres query. Right-click and copy the binary hex string from SQL Server Management Studio. Paste into a string in the PG Admin III SQL Editor window. Delete the "0x" at the beginning of the string. Wrap that value with a decode. Now ST_GeomFromWKB has WKB. ...


2

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


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.


2

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


1

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


1

If I understand correctly you have a paper plan of city and you want to create a table with geometries and ref numbers from readed 'manualy' coordinates from this paper map? Assuming that: Your table name is parcels It have column ref_number and geom The query could be: insert into parcels(ref_number,geom) values (1,ST_makepoint(wsp_x, wsp_y)) You ...


1

I think it's a bit simpler than that. PostGIS natively understands HEX. I think you can do this -- here is a string I got from SQL Server STAsBinary() output (after stripping off the 0x) SELECT '0101000000000000000000F03F0000000000000040'::geometry;


1

The pgRouting workshop gives a simple example how to return the path geometries. But this would work in the same way for any other attribute: SELECT seq, id1 AS node, id2 AS edge, cost, b.the_geom FROM pgr_dijkstra(' SELECT gid AS id, source::integer, target::integer, ...


1

Yes. that SELECT gid as gid .... is the magic part.If you have have "name" column in your table you can just add SELECT gid as gid, name as road_name ... FROM tablex if your name column is on another table then you need to to something like this. SELECT a.gid as gid , b.name as name ... FROM tablex as a , nametable as b WHERE a.gid = b.gid (or you can do ...


1

Solved: It wasn't clear about the dependency requirements for pgadmin when you install it. Found the files, (wxGTK-Base-2.8.12-1.elgi386.rpm)


1

You might have to add the following to your plugins.ini file: ; ; pgShapeLoader (OSX): ; Title=PostGIS Shapefile and DBF loader Command=/usr/bin/open /Applications/OpenGeo/pgShapeLoader.app --args '-U $$USERNAME -d $$DATABASE -p $$PORT -h $$HOSTNAME' Description=Open a PostGIS ESRI Shapefile or Plain dbf loader console to the current database. ...


1

It looks like the loader you're using is from 1.5 or lower, while the database you're loading to is 2.0. Either (a) move to the latest loader or (b) add the "legacy.sql" file into your postgis database to ensure that all the old function signatures the old loader expects are available to you.


1

The problem is with "NAME_3" = NULL, since NULL is not "equal to" NULL. This should instead use the proper SQL construct "NAME_3" IS NULL. See the documentation for more details.


1

It seems like your database is not PostGIS enabled. Make sure you created the database from your PostGIS template. You'll be able to tell by looking at the list of available functions. If you don't find an AddGeometryColumn function there, PostGIS support is missing.


1

Step1. Create folder name: plugin.d in folder PgAminIII Ex: C:\Program Files\OpenGeo\OpenGeo Suite\pgsql\9.1\pgAdmin III\plugins.d Step2. Create files config for app you need in new plugins folder EX: postgis.shp2pgsql-gui psql detai file: postgis.shp2pgsql-gui: ; ; PostGIS shp2pgsql-gui (Windows): ; Title=PostGIS Shapefile and DBF loader 2.0 ...



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