Hot answers tagged postgresql
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 ...
16
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 ...
14
This is a four stage process:
In the geometry_columns table, update the SRID to the required value.
Drop the contraint on the table, by using the following SQL statement
ALTER TABLE mytable DROP CONSTRAINT enforce_srid_the_geom;
Update the SRID'd of the geometry by using the following SQL statement
UPDATE mytable SET the_geom = ST_SetSRID(the_geom, ...
13
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 ...
13
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 ...
12
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 ...
12
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 ...
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)
;
10
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.
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
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 ...
9
I was in the same boat as you just a couple of weeks ago and was able to get a shortest path with relative ease by using the tools/information below:
As previously mentioned, I use pgAdminIII as my GUI for writing SQL and managing tables.
As for getting started, I would definitely start by following along with @underdark's excellent A Beginner’s Guide to ...
9
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 ...
9
The solution can be built entirely in PostGIS.
Given a point (house location, i've modeled it as a POINT) and a street segment (segment of street closest to this point, modeled as LINESTRING) you ask:
How to tell if point is to the left of a street segment
A possible solution is to determine the point on the street segment that is closest to the house ...
9
Based on the comments above, as I see it, you have a couple of options. The first is to use an existing Open Source GIS like QGIS or GRASS. You can customise both of these by editing the source code and/or writing pulgins (the former option being more radical and the latter more akin to using Arc Objects). Both support Python.
Your second option is to ...
9
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 ...
9
I think you will have to decide on certain criteria that define equality for you and that are likely to occur in your application. It is very hard to define likeness for all cases that are "visually" similar. For example: Is a clockwise circle the same as a counterclockwise circle in the same location? Is a circle the same as its approximation with straight ...
9
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 ...
8
You can use CASE to create classes. Something like that:
SELECT
d.disease_type,
ST_ConvexHull(ST_Collect(d.the_geom)) As the_geom
FROM
(
SELECT
diseaseobs.the_geom,
(CASE diseaseobs.number WHEN number BETWEEN 0 AND 1.5 THEN 'type one'
WHEN number BETWEEN 1.6 AND 3 THEN 'type two'
ELSE 'other'
END ) as ...
8
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.
8
Try replacing
'POLYGON((-1572563.9181513 7593219.6338481,
with
'SRID=900913;POLYGON((-1572563.9181513 7593219.6338481...
The first definition does not list the SRID so it's not using a SRID at all, hence the mismatch with 900913:
osm=# select srid('POLYGON((1 1,2 2,3 3,4 4,1 1))'::geometry);
srid
------
-1
8
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 ...
8
I sat in on a PostgreSQL/ArcSDE presentation (link1, link2) at this weeks Esri conference and they talked a little about this. They Esri product engineer said (at least I interpreted what she said) that:
ArcGIS 10.1 only supports PostGIS 1.5
ArcGIS 10.1 service pack 1 will support simple geometry features from PostGIS 2.0, but not advanced postgis ...
8
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, ...
7
Two Solutions:
1)Create a single sequence and make all the tables use that sequence, can be done from the beginning or you may create a ID column and update your tables now.
To Create the sequence:
CREATE SEQUENCE universal_sequence;
Then a table:
CREATE TABLE (
colname integer NOT NULL DEFAULT nextval('universal_sequence'));
To Update an existing ...
7
Postgresql is quite good handling multi-cores, especially since every connection gets spawned to a new process and thereby gets handled by the OS process scheduler.
I've run large postgresql databases on both windows and linux (ubuntu server) and they both perform very well.
However, most likely your performance will be heavily dependent on how well your ...
7
I just found the solution.
After the steps above, one has to actually add the functions to the postgresql database:
$ sudo su postgres
$ psql -d routing -f
/usr/share/postlbs/routing_core.sql
$ psql -d routing -f
/usr/share/postlbs/routing_core_wrappers.sql
$ psql -d routing -f
/usr/share/postlbs/routing_topology.sql
I hope this ...
7
Something like
SELECT
source,
target,
(SELECT SUM(cost) FROM -- or whatever you want to do with the routing result
(SELECT * FROM shortest_path_astar('...',
source,
target,
false,
false)
) AS foo
) AS cost
FROM all_to_all;
7
I can get you part of the way there by assuming you have figured out how to request (a) the easternmost half of a set of points and (b) the northernmost half of a set of points. From these you can, of course, easily obtain (c) the westernmost half or (d) the southernmost half. (I don't know QGIS, but one way to do (a) in general is to request the median ...
Only top voted, non community-wiki answers of a minimum length are eligible
