Tag Info

Hot answers tagged

38

There is a single line function which does this for you. Just use the following SQL query: select UpdateGeometrySRID('Schema Name', 'mytable', 'the_geom', newSRID) ; But, if you are like me, you would be interested in the low level, miniature steps. Logically speaking, the above function is equivalent to the following four step process: In the ...


34

You are confusing SQL and WKT (well-known text). WKT is a like a geometry language to describe shapes, but it is not SQL, which is a language to query and manipulate databases. When working with WKT in an SQL query, it must be text, and not mixed-in with the SQL. Your query works if you properly format the WKT (remove the ",") and set an SRID. For this ...


19

You can quite easily create the template if it is not there automatically. Here is a description for ubuntu: http://obsessivecoder.com/2010/02/01/installing-postgresql-8-4-postgis-1-4-1-and-pgrouting-1-0-3-on-ubuntu-9-10-karmic-koala/ This is the essential part: sudo su postgres createdb template_postgis createlang plpgsql template_postgis psql -d ...


17

You should be able to use the row_number() function as a column in your view. This works for Postgres 8.4 or higher. http://www.postgresql.org/docs/current/static/functions-window.html SELECT * FROM ( SELECT ROW_NUMBER() OVER (ORDER BY column_to_sort_by ASC) AS ROW_NUMBER, Col1, Col2 FROM table_name ) myview_name This should work in most ...


16

Give a look at this post on my blog: http://www.paolocorti.net/2008/06/06/spatial-database-for-postgres-and-arcgis-users-how-to-choose/ Basically you have 2 options: use PostGis with ArcSde (so you need an ArcSde license, and ArcEditor if you need to edit data) use zigGIS: http://www.obtusesoft.com/ (note that is not tested on ArcGis 10.0). You will just ...


15

Since Postgis is a component of Postgres I would recommend this great book (I own it and I found it extremely valuable) on Postgres performance tuning: http://www.packtpub.com/postgresql-90-high-performance/book It starts from the basics (planning the hardware, os, etc) and then grows into explaining all those misterious configuration params that I never ...


15

Try shp2pgsql. The basic syntax is like: shp2pgsql -s SRID SHAPEFILE.shp SCHEMA.TABLE | psql -h HOST -d DATABASE -U USER I always find this cheatsheet from http://www.bostongis.com useful. If you scroll down a little, you will find simple examples on how to load data. Hope it helps.


15

A great solution is to use QGIS as you gui for PostGIS. QGIS is a fully-functional GIS (I prefer to think of it actually as a meta-GIS given that it is built on many GIS packages). It has built-in support to connect to PostGIS and therefore gives you all the tools tou need to view, edit and create maps from your data.


14

PostGIS in Action, which has been released last month is a good book for worth reading. PART 1 - LEARNING POSTGIS 1 - What is a spatial database? Completed download chapter code and data 2 - Geometry Types Completed download chapter code and data 3 - Data Modeling Completed download chapter code and data 4 - Geometry Functions Completed download chapter ...


14

If you don't need third party support and don't forsee the need to query by type keeping them in the same table works just fine. Alternatively you could use an inheritance model as discussed in chapter 3 of PostGIS in Action. http://www.postgis.us/chapter_03 From an architecture perspective PostGIS doesn't really care if in a query multiple different ...


14

From version 2 Postgis is enabled by using the extension system. To spatially enable a database, log to your database and then: CREATE EXTENSION postgis; CREATE EXTENSION postgis_topology; source: http://postgis.net/docs/postgis_installation.html Note: Only SUPERUSERS roles have the ability to create EXTENSIONS


13

Generally you can make a new geospatial table like this: SELECT * INTO europe.borders FROM wo_borders WHERE admin_lvl2 = 'eu'; -- Define a primary key ALTER TABLE europe.borders ADD PRIMARY KEY (gid); -- Spatially enable it SELECT Populate_Geometry_Columns('europe.borders'::regclass); However, by doing this you are segregating your database ...


12

ST_Distance only calculates the distance between two features "as the crow flies". pgRouting on the other hand calculates the actual distance along a network (e.g. road network). Those are two different things and it depends on your use case whether ST_Distance is sufficient or not.


11

Check BostonGIS Compare SQL Server 2008 R2, Oracle 11G R2, PostgreSQL/PostGIS 1.5 Spatial Features. If that's too basic: Which topics do you need covered? MySQL is not usable for serious GIS work.


11

Unless you have used the -x switch then the database that osm2pgsql creates won't include any contributor information. The main use for those databases is rendering which doesn't normally need to know who created an object, and including that information makes the database much larger, so it is normally left out. That information is in the planet file ...


11

If you group, you should get only unique points. CREATE TABLE test_points as SELECT ST_Intersection(a.geom, b.geom), Count(Distinct a.gid) FROM roads as a, roads as b WHERE ST_Touches(a.geom, b.geom) AND a.gid != b.gid GROUP BY ST_Intersection(a.geom, b.geom) ;


11

All spatial table references are held in the geometry_columns metadata table. So try: select * from geometry_columns and you should get just the spatial tables


11

The most efficient index for the query expressed in your question is the one on gid as it is the only column that appears in a where expression: CREATE INDEX table_gid ON table (gid); You can safely drop the gist index as it will only consume space and slow inserts/updates/deletes down. Long explanation As I said the most effective index in your case ...


11

you can find very detailed information about Jetty and Apache httpd (from http://wiki.eclipse.org/). i think you should decide what you want from a web server more precisely according to the expectations. Apache httpd is a HTTP server written in C, that is often used to front other web services. Jetty is a full functional and optimized HTTP server ...


11

If you have PostGIS driver capability in the rgdal package then its just a question of creating a connection string and using that. Here I'm connecting to my local database gis using default credentials, so my DSN is rather simple. You might need to add a host, username, or password. See gdal docs for info. > require(rgdal) > dsn="PG:dbname='gis'" ...


11

You're not doing anything wrong, you discovered a hole in our support for PostgreSQL 9.3's new materialized view feature. I've patched all the relevant branches, and you can update your definition of geometry_columns yourself (see the change references in this ticket http://trac.osgeo.org/postgis/ticket/2511) Actually, here's something you can just paste ...


10

I've found the problem. It was a self intersecting polygon. I used ST_IsValidReason to find it.


10

The service parameter in the QGIS/PostGIS dialog should be empty. And I spent a bit of time killing the user account "postgres" in windows and removing the names of the databases which are created in the windows registry. After that I could repeatedly fail the dialog by having something in the service parameter of the QGIS Postgis connection dialog.


10

You need to reference your table twice, giving it different aliases: SELECT ST_Distance(a.geom, b.geom) FROM points_table a, points_table b WHERE a.id='x' AND b.id='y';


10

First, make sure you have an index on your geography column. It will speed up the spatial searches: CREATE INDEX geo_cities_geog_idx ON geo_cities USING GIST geog; VACUUM ANALYZE geo_cities(geog); Then, you can use ST_DWithin (with conversions from miles to metres) on a self-joined query: SELECT gc.*, ST_Distance(gc.geog, pt.geog)/1609.344 AS ...


10

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


10

I may be too late, but in case someone else comes across this forum for the same problem (as I did ;)). This is because you have to install the correct files for your client: http://resources.arcgis.com/en/help/main/10.1/index.html#/Setting_up_a_connection_to_PostgreSQL/002p0000003q000000/. It took me a while to figure this out as the ESRI online ...


10

You should verify that you can connect using psql. Try psql -U username -h localhost dbname. It should prompt for a password then connect. Run SELECT postgis_version(); to verify that PostGIS is active in the database. If you can connect but SELECT postgis_version() reports an error, PostGIS isn't installed in the database: ERROR: function ...


10

PostGIS is an extension of the PostgreSQL database, you can't have PostGIS without PostgreSQL. PostGIS is a robust extension, it defines new datatypes, and provides hundreds of functions so that you can make use of your spatial data. When referring to the specific database you can say it is PostGIS enabled, or if your audience knows your talking about ...


10

As unicoletti said, the gist index in the geometry column would only work if you use ST_Contains() in the WHERE expression. For instance, if you would like to know all polygons that contain one another, you could use something like this: SELECT a.gid, b.gid FROM table AS a, table as b WHERE a.gid != b.gid and ST_Contains(a.way, b.way) In this case, ...



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