53
I just had the same problem on Ubuntu Server 14.04.
I installed the postgis extension from the official Ubuntu repositories using apt-get install postgis.
Then, find /usr -name postgis.control didn't return any results.
The reason was extension/postgis.control wasn't installed because postgis-scripts wasn't.
$ aptitude search postgis
i libpostgis-java ...
36
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.
geometry(Point,4326)...
18
In Ubuntu 14.04 you also need to install the postgresql-9.3-postgis-scripts package. After I ran
sudo apt-get install postgis postgresql-9.3-postgis-scripts
I was then able to successfully run
CREATE EXTENSION postgis;
in my database to initialise PostGIS.
18
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 0....
18
You can try ST_CollectionExtract to extract [Multi]Polygons from GeometryCollections. Use ST_Multi to force them as MuliPolygons.
UPDATE public.valid_lcmsouthshapefile
SET geom=ST_Multi(ST_CollectionExtract(ST_MakeValid(geom), 3))
WHERE NOT ST_IsValid(geom);
After your done, use a CHECK constraint to ensure they stay valid. See details here.
15
It's necessary to calculate the actual intersection, e.g. http://postgis.net/docs/ST_Intersection.html
SELECT ST_Length(ST_Intersection(line,polygon))
14
A self-join allows you to operate on the relationship between pairs of two features. But I don't think you're interested in pairs: for each feature, you want to operate on the relationship between that feature and all other features in your dataset. You can accomplish this with a subquery expression:
CREATE TABLE parcels_trimmed AS
SELECT id, ...
14
If you only want Polygons or Multipolygons from ST_MakeValid you can use ST_Dump to extract the constituent geometries and then test for the geometry type. ST_MakeValid will sometimes produce Points or LineStrings which is where the GeometryCollection is coming from. Try something like:
SELECT
g.geom,
row_number() over() AS gid,
FROM
(SELECT
...
13
Your coordinates are out of order. If you reverse the order of coordinates in the first query, postgis says: 708.55982691.
In postgis it's lon, lat, not lat, lon.
12
QGIS comes with "offline editing" functionality in core. This downloads the data to a local spatialite database, so you can work on an offline copy and the delay caused by network roundtrips is gone. This is not a memory layer but if I understand correctly, that's just an example and not mandatory.
To enable offline editing, you have to check the offline ...
11
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 ...
11
If you can find a postgis.sql file, you can run that into your database (and the spatial_ref_sys.sql file) to manually spatialize your database. Report the missing control file to the packager, that's a big problem.
11
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 >...
10
Here's a query that should get you what you want:
SELECT a.name, b.name, ST_LENGTH(ST_Intersection(a.geom, b.geom))
FROM polygons a, lines b
WHERE ST_Intersects(a.geom, b.geom);
10
This is a case sensitivity/quoting issue.
"PointsForGpxExport" and PointsForGpxExport are not the same table name.
PostgreSQL, per the SQL standard, case-folds unquoted identifiers. (It case-folds to lowercase, where the standard says uppercase, though). So when you write PointsForGpxExport, PostgreSQL treats that as the same as pointsforgpxexport. Since ...
10
Be aware that the GeoJSON specification states that
The GeoJSON object must have a member with the name "type". This member's value is a string that determines the type of the GeoJSON object.
http://geojson.org/geojson-spec.html#geojson-objects
If you decide that you do not want/need to be compliant with the GeoJSON spec, you can use the - (minus) ...
9
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 -...
9
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 ...
9
In a generic sense, use the affine transform parameters, which should be available with any raster file format. With GDAL, this is available with GetGeoTransform(), or PostGIS' ST_GeoReference() function. After finding these six parameters, one only needs to determine which ones they are, then a function can be made to transform in linear space.
E.g., with ...
9
You are looking for PostGIS 2.0 Shapefile and DBF Loader Exporter, so access "C:\Program Files\PostgreSQL\9.3\bin\postgisgui\shp2pgsql-gui.exe"
9
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$$;
...
9
Internally, ST_Buffer(geography, ...) uses a fixed projection guess with _ST_BestSRID, which are typically UTM zones or whatever makes sense to the algorithm. This is why you see the differences, because they are different projections that are not optimized for the location of the points.
For simple point buffers, you could use a custom azimuthal ...
9
It might be required in C:\Program Files\PostgreSQL\10\bin\postgisgui (or wherever the loader is installed).
It is however suspicious that the lib isn't included in the installer, and it might reveal other issues
8
You will definitely want to tile your raster. The maximum width x
height permitted for the PostGIS raster type is 65535 x 65535,
regardless of whether or not the raster is in-db or out-db. The other
reason to tile your raster (though this may not apply in your case) is
that the maximum field size permitted by PostgreSQL is 1 GB [1].
As for optimal tile ...
8
You can use a typmod to specify the srid in the geometry_columns view, something like
CREATE VIEW pippo AS
SELECT st_geometryn(shape,1)::geometry(Geometry, 4326) as geom
FROM events
The manual has more info.
8
I have found a solution to my problem: All the needed information can be found at this link
https://postgis.net/docs/using_postgis_dbmanagement.html#Manual_Register_Spatial_Column
Solution 1: Create the table and add the geometry column typmod based, the creation process would register it correctly in geometry_columns.
Correct: CREATE TABLE pois_ny(gid ...
8
What does the “service” box in the PostGIS connection dialog stand for?:
The service entry allows you to specify a service file that contains the connection parameters needed to access a certain postgresql database.
By having a service file, you can then ignore having to enter other details like database, host, port, user and password.
Here are some of ...
8
Assuming your data table look like:
streetid housenumber
100 12
100 15
101 12
101 18
You could do a basic SQL query:
SELECT streetid, count(*) FROM tablename GROUP BY streetid
Where this might get more GIS-y and interesting is the case where streets with the same ID are not contiguous. You didn't mention if this is a ...
8
I made and tested the functions I need. For point generation I used a simplified version of the functions in trac.osgeo.org/postgis/wiki/UserWikiRandomPoint:
CREATE OR REPLACE FUNCTION random_point( x_min integer DEFAULT 13, y_min integer DEFAULT 49,
x_max integer DEFAULT 16, y_max integer DEFAULT 51, srid integer DEFAULT 4326 )
RETURNS geometry AS
$...
7
For those who will stumble on this question like me..
It appears that the plugin has been generated (https://github.com/strk/mapnik/tree/2.3.x-pgraster) and has been merged in official Mapnik Repo.
The branch is 2.3.x (https://github.com/mapnik/mapnik/tree/2.3.x)
Now you can build Mapnik from the branch and use PGRaster plugin to use Raster data from ...
Only top voted, non community-wiki answers of a minimum length are eligible
