Tag Info

New answers tagged

0

You can use Spatial Manager Desktop to import OSM data into PostGIS. Please, watch this post: http://www.spatialmanager.com/import-directly-postgis-databases/ There is a limited trial version ,you can download it here: http://www.spatialmanager.com/downloads/ If you need help on this feel free to tell me. Disclaimer: I work for Opencartis (Spatial Manager ...


3

You can use ogr2ogr with the -sql option to use a sql statement


0

I solved the problem, thanks to @Underdark. The error was in the instruction qgsVectorLaye: myVectorLayer = QgsVectorLayer(uri.uri(), "allcountries", 'postgres')


1

myVectorLayer = QgsVectorLayer("polygon", "mypoly", 'ogr') does not create a valid vector layer, therefore mySymbol1 = QgsSymbolV2.defaultSymbol(myVectorLayer.geometryType()) does not return a valid symbol and causes the error you see. Replace "polygon" with a valid file path.


0

I agree with the other comments, for it to be this slow you need to not have setup indexing correctly. How are you storing the data ? Are you using JSON ? You need to have spatial columns or indexes to get fast queries working. Check out the links below for examples of spatially indexing JSON twitter data. ...


0

use "explain" (http://www.postgresql.org/docs/9.3/static/sql-explain.html) to see if your spatial index is being used, and make sure you've run "analyze" (http://www.postgresql.org/docs/9.3/static/sql-analyze.html) to make sure that the db has collected sufficient statistics on your indices. It sounds to me like you don't know what you're doing with respect ...


2

You've switched the order of your lat,lon. See the documentation for ST_MakePoint() and you'll see that it requires: ST_SetSRID(ST_MakePoint(lon, lat),4326) Then, after to make your changes, try verifying: SELECT ST_AsText(location) FROM *tablename* LIMIT 10;


1

AFAIK there is currently no 'best practise' on how you organize the OSM data at your PostGIS backend. There are different database schemas that are tuned for specific purposes: rendering geospatial lookups / reverse geocoding fulltext search / gazetter ... If you have a very specific usecase, you might tweak the OSM importer mappings (e.g. at osmosis or ...


2

JJD yes there is. I haven't used it myself. You use the -m option and pass a mapping file that has the old column name and new column name on each line. As Ryan alluded to here: http://www.bostongis.com/pgsql2shp_shp2pgsql_quickguide.bqg I suspect he was thinking of -m and mistyped -f. Hope that helps, Regina


0

Well you can let it be created on the "public" schema and after: - ALTER TABLE IF EXISTS public.actions SET SCHEMA gis; - ALTER TABLE IF EXISTS public.schema_info SET SCHEMA gis; - ALTER TABLE IF EXISTS public.users SET SCHEMA gis; - ALTER TABLE IF EXISTS public.way_nodes SET SCHEMA gis; - ALTER TABLE IF EXISTS public.relations SET SCHEMA gis; - ...


1

You can't. Table inheritance is a bit limited. In particular, you can't have a foreign key that references all parts of an inherited table set, and you can't define triggers or constraints that're inherited by a set of tables.


2

I usually do this using QGIS. I load the kml layer, save as shapefile and add the columns that are necessary for rendering. Then I open a connection to my postgis database, delete everything inside the current view, and copy-and-paste the kml data into the postgis layer. I have created a separate bboxdb inside postgis for such cases, so my original osm ...


2

Well, before you convert the database, you could run the Domain to Table geoprocessing tool http://resources.arcgis.com/en/help/main/10.1/index.html#/Domain_To_Table/001700000022000000/ which should convert a domain to a stand-alone table that you could put in the database. Then, you should be able to just convert the database, including stand-alone tables ...


0

You've got few ways to do this: Draw the point in upper left corner of geometry and order by distance from this point Order by added coordinates of upper left vertex of both geometries Select * from ( SELECT ((ST_Dump(ST_Split(circle, line))).geom) As wkt FROM ( SELECT ST_GeomFromText('LINESTRING(73.3864598921383 ...


1

What do you mean by no spatial view. You mean you are not seeing it in geometry_columns table or your viewer is not loading it? If you are using PostGIS 1.5 or lower, you have to manually add the view to the table geometry_columns table. In PostGIS 2.0+, it will show in list but won't show type. To get type you need to do cast it. Also many views like ...


0

There is likely a problem with your SQL in the subquery... If you are putting this query as the subquery EXACTLY... SELECT * FROM shortest_path_astar(' SELECT gid as id, source::integer, target::integer, length(the_geom)::double precision as cost, x1, y1, x2, y2 ...


0

So, after following Carsten's suggestion from the comments, I could finally generate a routing database for the entire world. The catch was to use the tileSize command line argument from osm2po. The actual values that I used were java -Xmx8192m -jar osm2po-core-4.8.8-signed.jar prefix=world tileSize=10x10,1.0 planet-140430.osm.pbf in case you are wondering ...


0

It seems you encountered a strange bug inside the osm importer. It expects the nodes to be ordered, but they are not. Using QGIS, I get the same strange 4-node polygon using Add Vector layer from GDAL , but the Openstreetmap plugin does it right (i.e. it self-orders the nodes). It looks like osm2pgsql expects the nodes ordered as well, but the export ...


0

I finally managed to resolve my problem. My solution was to load data into temporary spatial table on PostGIS server, delete all records from table and then insert all records from temporary table to database. DELETE FROM public.target_table; INSERT INTO public.target_table SELECT * FROM public.source_temporary_table; All you need is a table with ...


0

Would you be able to try loading your layers directly into QGIS from PostGIS? See http://docs.qgis.org/2.2/en/docs/user_manual/working_with_vector/supported_data.html#postgis-layers


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

SELECT cdb_usertables, pg_size_pretty(pg_total_relation_size(cdb_usertables)) FROM CDB_UserTables() ORDER BY pg_total_relation_size(cdb_usertables) DESC The CDB_UserTables() function is mentioned in another question regarding getting a list of all tables. It returns one column named after the function. pg_total_relation_size() "accepts the OID ...


0

I use both GraphHopper AND Pg_Routing Pg_Routingis a lot more flexible but is relatively slower (Depending on the size of the area). You can change costs on an edge in real time GraphHopper buids graphs from an OSM Extract and has its own built in web Server, so is very quick to get up and running ..


1

If you're a very beginner you should try this tutorial of osm2po by @Underdark Osm2po will give you ready to use graph from osm data as sql script file, all you have to do is load it into database via psql -f command. In this table every single record is edge. Every edge has 2 vertices source and target - coordinations of this points are in column x1,y2 ...


1

You should check the pgRouting project. There is a great step to step workshop/tutorial. http://workshop.pgrouting.org/


0

Well, others have tried well to answer my question. But those are not according to my requirements. What I did in this case is to handle the case at Server-end. Using PHP array, get the dumped geometries: while ($row = pg_fetch_assoc($rs)) { $result[] = $row['wkt']; } By using above snippet each dumped geometry is stored at an ...


0

Try to use function st_setsrid() to set srid of your data (if it's geographical coordinates use 4326) or function st_transform() to transform data from one srid to another. Last (but not least) if your data is in geographical coordinates you can use datatype geography instead of geometry.


0

create view track_with_distance as select to.*, cast(case when from.latitude=to.latitude and from.longitude=to.longitude then 0 else 1852*acos(sin(from.latitude/180*pi())*sin(to.latitude/180*pi())+cos(from.latitude/180*pi())* cos(to.latitude/180*pi())*cos(from.longitude/180*pi()-to.longitude/180*pi()))* ...


2

I imported a Planet File on a 24Gb Machine (Ubuntu Trusty) with the following .. bzcat planet-latest.osm.bz2 | osm2pgsql --verbose -U YourUser --flat-nodes flat-nodes --keep-coastlines --cache 24000 --hstore --hstore-add-index --tablespace-index pg_default --exclude-invalid-polygon --number-processes 6 --unlogged --cache-strategy dense --extra-attributes ...


2

Your computer should be fine for importing Europe. Given your dataset size and computer, I'd recommend something like this osm2pgsql -c -S /usr/share/osm2pgsql/default.style --slim -d osm-europe --flat-nodes flat-nodes.bin --number-processes 8 -C 20000 europe-latest.osm.pbf I'm assuming that you have an 8 thread CPU, if not, adjust --number-processes. ...


1

As this message show: UnicodeDecodeError: 'utf8' codec can't decode byte 0xc4 in position 0: invalid continuation byte It is likely due a problem with character encodings. Maybe your database is using ASCII instead of UTF8. To check what character encoding is using your database, you cold ask your postgreSQL server with PGAdmin III or psql. If you have ...


0

I asked this over on https://help.openstreetmap.org/questions/34315/issue-importing-tiger-data-into-nominatim and it turns out there was a bug in nominatim which is now fixed


0

The OpenJUMP error message resends the error from PostgreSQL database. The message tells that AddGeometryColumn function does not exist and that indicates that the GIS database is missing PostGIS. PostGIS extension is not installed automatically to new databases but it must be explicitly installed by using SQL CREATE EXTENSION postgis;


6

There's two things going on here: the GIST API in PostgreSQL and the bindings of types to that API for the purposes of building an R-Tree. PostGIS necessarily uses the PostgreSQL GIST API. That's what it's for. That way we don't have to worry about transaction management or writing things to disk or all the other messy important things involved in ...


2

In the PostGIS documentation it says: PostgreSQL supports three kinds of indexes by default: B-Tree indexes, R-Tree indexes, and GiST indexes. ... GiST (Generalized Search Trees) indexes break up data into "things to one side", "things which overlap", "things which are inside" and can be used on a wide range of data-types, including GIS ...


0

You can store images in postgresql using the following function : create or replace function bytea_import(p_path text, p_result out bytea) language plpgsql as $$ declare l_oid oid; r record; begin p_result := ''; select lo_import(p_path) into l_oid; for r in ( select data from pg_largeobject where loid = ...


0

I'm assuming that you are using Esri objects to connect to PostgreSQL. Each row needs to be updated one row/feature at a time. A row and a feature are identical to a database in that a feature is a row with a geometry registered with the database. In the same way a FeatureClass is a table with a geometry column. At some stage you have a featureclass, that ...


1

I'd go with the Geometry Accessors Width: ST_XMax - ST_XMin, and the related functions for Y to give height.


1

This worked for me. Change the line in the Dockerfile that has the install to use 2.0 postgis: RUN apt-get -y -q install postgresql-9.3 postgresql-client-9.3 postgresql-contrib-9.3 postgresql-9.3-postgis-2.0


1

please see official documentation. they are great! see in this post when to use what and why. generally it is a matter of complexity. some tasks requires less gun power and some need more.


0

A month ago a friend of mine asked me to convert the topology for all walkable streets of the planet file with osm2po. We finally agreed to exclude motorways but to include everything from trunk to track. It took about 5 hours on (don't ask me for details) a dedicated linux server with 24Gig RAM. Okay, osm2po does lot's of preprocessing stuff and applies ...


0

Try to check the environments, namely processing extent of your project. Maybe while drawing a new feature, parts of it are founded out of extent bounds.


0

I believe one can still import the data with the Map_Tools. This is a java Program: once a connection is established from the map tool to a DB, one may start importing the data with Tools->RDF Installer


1

I was able to import a full planet file in about 5 days using osm2pgsql with almost the same machine specs you have. You could do that, then use the PGRouting Calculate Topology functions (plus the other info on that page) to build your network. This has the added advantage of giving you a PostGis database with all the tags as well to use for queries or ...


0

Oddly, I can only reproduce your error with your comment WHERE ST_IsValid(ST_AsGeoJSON("wkb_geometry")) which is attempting to export a geometry to a JSON string, then parse the text to a back to a geometry for ST_IsValid, which will raise a parse error since it expects WKT. ERROR: parse error - invalid geometry HINT: "{"" <-- parse error at ...



Top 50 recent answers are included