More

Importing .shp to PostgreSQL?

Importing .shp to PostgreSQL?


I want to import a shp file to postgreSQL. First I create sql file and then run PostgreSQL. To create sql file, I run this command in windows cmd:

shp2pgsql -s 4326 worldCountries.shp worldcountries postgres > worldcountries.sql

and then run:

psql -d postgres -U postgres -p 4321 -f worldcountries.sql

but result is:

psql: worldcountries.sql: 21: ERROR: function addGeometrycolumn(unknown, unknown, unknown, unknown,unknown, integer) does not exist


ERROR: **function addGeometrycolumn**(unknown, unknown, unknown, unknown,unknown, integer) does not exist

It seems that PostGIS is not yet installed. PostGIS is an extension of Postgres which allows the use of geographic files.

Install it and your import will work fine.


TheERROR: function addGeometrycolumn(unknown, unknown, unknown, unknown,unknown, integer) does not existmeans that the postgis extension is not installed on your postgresql. Go here and install it.


You have a different projection in your psql; and having a projection is not necessary. Is there a schema you want to add the shape file to?

This should work:

shp2pgsql -s 4326 -I worldcountries.shp schema.worldcountries > dr_worldcountries.sql psql -f worldcountries.sql -h [host] -d [database] -U postgres

You can use the build in import plugin that is installed when you install the PostGis extension in PostgreSQL You can find it under Plugins in the menu and is called the PostGIS Shapefile and DBF loader

watch this youtube video as an example


A "shapefile" is actually more than one file (i.e., more than just a .shp file). Make sure all of the files are in the same folder when using with any GIS software, including shp2pgsql.

The minimum required files, sharing a common prefix, are:

Other files may include: .prj, .sbn, .sbx, .fbn, .fbx, .ain, .aih, .ixs, .mxs, .atx, .shp.xml, and .cpg.

After having the same problem and moving files around to smaller paths did not help, I tried with the full path of the .shp file instead of reffering only the filename while being in the same folder.

Fortunately this was my problem and it worked after that.

I have encountered the same problem and I found out that the path to the (.shp) and (.dbf) file contains space.

e.g. the path to your target shape file is home/my name/shapefiles/myshape.shp which contains a (a space) which the terminal doesn't recognize.

The solution is to add a backslash followed by a space like instead of a single space like .

In my example, type the path in the terminal the like home/my name/shapefiles/myshape.shp and it works just great. Try it out.

Similar to the way we handle space in string in most programing languages.


PostgreSQL

PostgreSQL is an open source, enterprise class database system.

Shapefile (SHP)

The Shapefile format is a vector data format that stores the location, geometry, and attributes of geographic features in a set of related files.

FME is the data integration platform with the best support for spatial data. Save time by using its drag-and-drop interface to connect data from hundreds of formats and applications, transform data in limitless ways, and automate virtually any data workflow.


PostGIS

PostGIS is a spatial extender for the PostgreSQL open source, enterprise class database system that adds support for geographic objects. It is an appealing solution for organizations who don't wish to be tied to vendor software.

PostgreSQL

PostgreSQL is an open source, enterprise class database system.

FME is the data integration platform with the best support for spatial data. Save time by using its drag-and-drop interface to connect data from hundreds of formats and applications, transform data in limitless ways, and automate virtually any data workflow.


GIS Implementation Process

Geographic Information Systems (GIS) are fundamental tools for learning geography. It is a system (hardware + database engine) that is designed to efficiently, assemble, store, update, analyze, manipulate, and display geographically referenced information (data identified by their locations). GIS therefore encourages peoples to think spatially, or geographically. GIS is much more than a container of maps in digital form. GIS is a spatial decision support system.

2. Process for implementation of GIS

The most effective way to make GIS successful during plot management is to use it throughout the preparedness efforts. GIS development practices pursues the below workflow.

(Workflow of GIS)

Broadly GIS activities are performed in two different categories, such as GIS Development and GIS Integration. Let us step by step understand the background of each of these GIS activities.

1.1. GIS development

Below are the GIS practices and procedures by following which application owner will be able to manage GIS based application efficiently.

1.1.1. GIS Data Assembly

Data can be collected from the various sources and techniques to be ported in to GIS environment. The common ways are, digital imaging which substitutes the classic image of photogrammetry for a better depiction of the area hence better data collection. Laser scanners and SAR (Specific Absorption Rate) which allow the easier illustration of areas like forests when it is difficult to illustrate them in other ways. Cameras equipped with geographic information system technologies for immediate, automated and directly digitized results, mainly about resource potentials. Stereo images from high resolution and close-range photogrammetry with ortho photos, digital surface model etc. Of course, the main sources are GPS, remote sensing and photogrammetry but all the mentioned methods and applications are useful too.

(Data Assembly)

1.1.2. Data Preparation

Preparing data means ensuring datasets can be validly analyzed together and reducing processing time as much as possible. Data preparation tasks often include projecting data, reducing the spatial extent to the area of interest, deleting unneeded attributes, creating new attributes, cleaning up attribute values etc. This can be achieve through ArcGIS geo processing tool.

(Data Preparation)

1.1.3. Data Storage

There are various way provided by ESRI for storing spatial data. They encourage you to put your data in geodatabases, which are organizational structures for storing datasets and defining relationships between those datasets. Different flavors of geodatabase are offered for storing different magnitudes of data.

  • Personal geodatabases are a small, nearly deprecated form of geodatabase that store data on the local file system. The data is held in a Microsoft Access database, which limits how much data can be stored in the geodatabase.
  • File geodatabases are a newer way of storing data on the local file system. The data is stored in a proprietary format developed by Esri. A file geodatabase can hold more data than a personal geodatabase: up to terabytes.
  • ArcSDE geodatabases or “enterprise geodatabases” store data on a central server in a relational database management system (RDBMS) such as SQL Server, Oracle, or PostgreSQL.

(Storing special data)

1.1.4. Spatial Data Analysis

The common way of spatial data analysis are calculating the total area in each land use category. Create spatial summaries, such as calculating the average elevation for each watershed. Summary data is useful for gaining a better understanding of conditions in a study area.

In the below figure, summary statistics have been calculated for each land use class showing the number of parcels in that class, the size of the smallest and largest parcel, the average parcel size, and the total area in the class.

(Spatial Data Analysis)

1.1.5. Spatial Data Output

Shapefiles are a simple, non-topological format for storing the geometric location and attribute information of geographic features. A shape file is one of the spatial data formats that you can work with and edit in ArcGIS.

The shape file format defines the geometry and attributes of geographically referenced features in three or more files with specific file extensions that should be stored in the same project workspace. They are:

  • .shp—The main file that stores the feature geometry.
  • .shx—The index file that stores the index of the feature geometry.
  • .dbf—The dBASE table that stores the attribute information of features.

There is a one-to-one relationship between geometry and attributes, which is based on record number. Attribute records in the dBASE file must be in the same order as records in the main file.

When viewing shapefiles in ArcCatalog (or any ArcGIS application), you will only see one file representing the shape file however, you can use Windows Explorer to view all the files associated with a shape file. When copying shapefiles, it is recommended that you do so in ArcCatalog or by using a geo-processing tool. However, if you do copy a shape file outside ArcGIS, be sure to copy all the files that make up the shape file.

(Spatial Data Output)

1.2. GIS Integration

This GIS integrations are carried out post deployment of the spatial data in ArcGIS Server (WebGIS). These functions are different from preparation of GIS spatial data (.shp file). This is purely a web development activity, where are building spatial data is a digitization activity. Steps to be followed for the deployment of spatial data in GIS server and integration in web based MIS application are as follows:

Following image depicts the step by step process followed currently for data updation in web GIS platform.


Create automated workflows that transform data stored in PostGIS databases into Shapefile (SHP) for use in ArcGIS. More than a data translator, FME's tools allow you to remodel the structure and contents of a dataset from PostGIS so that it will load properly into ArcGIS and function exactly how you need.

FME is a productivity tool designed to take you out of the transformation process as much as possible. Once constructed in an intuitive graphical interface, PostGIS to Shapefile (SHP) workflows execute with the click of a button and run automatically in the background while you work on other things. They can also be saved and reused for ongoing conversion needs.

Download a free 30-day trial of FME and discover how it streamlines PostGIS to Shapefile (SHP) conversions and seamlessly connects the two applications. With support for 345+ formats you may find it helps you overcome many other data interoperability challenges.


It is possible to download map data from the OpenStreetMap (OSM) dataset in a number of ways. The full dataset is available from the OSM website download area. This dataset (Planet.osm) is a large file, currently about 4GB when compressed and about 100GB when uncompressed. It is also possible to select smaller areas to download. Data normally comes in the form of XML formatted .osm files (an OpenStreetMap markup language).

I have downloaded today a part of the city of Porto using the OSM API, converted it into shape files, added a new store to geoserver (using those shape files) and finaly visualized the new layers in GoogleEarth and uDig.

First you will have to define your region specified by a bounding box which consists of a minimum and maximum latitude and longitude. An easy way to accomplish that task is to use Google Earth. Hovering the mouse in the desired corner of the rectangle to export, look at the coordinates and take notes (you only need two coordinates – opposite corners of the rectangle).

Then use those values to create the HTTP request using the folowing URL:

Where left is the maximum longitude, right is the value for the minimum longitude, bottom the minimum latitude and top the maximum latitude.

However there is a constraint, the API is limited to bounding boxes of about 0.5 degree by 0.5 degree. For larger areas you could try Osmxapi like so (this will also return a .osm file):

The .osm file is not currently not supported by geoserver, so we got 2 ways to get around this. Convert it to shape files, or insert directly in PostGIS database. However I discovered a Web page that offers subsets of the original OSM dataset. They do this by dividing the datasets by country offering the information both in .osm or .shp files, very handy.

Not easy to find, cause I did it in Windows OS, but there is an application out there called osm2shp, which comes with a GUI that does this conversion in a very easy way. If having trouble finding it, just ask me. If you are working in linux, you’re be glad to know that Ubuntu (and Debian-based OSes) repositories have this package.

The output of the previous convertion can be copied to the data folder in the geoserver path. Just create a folder (for instance osmdata) containing the shape files in:

then create a new datastore using the GeoServer Web interface. (check this tutorial)

Just pay attention so that the projection of the source data be configured as 900913, the projection used in Google Maps, and the default for OpenStreetMap.

There is a converter that allows you to load the OpenStreetMap data into PostGIS called osm2pgsql. This program is a package available on Debian-based distributions (such as Ubuntu), and is also available as a binary on Windows.

osm2pgsql -E 900913 -d myDataBase TheOsmFile.osm

This will process the XML information and load the data into a PostGIS database called “myDataBase”. The -E defines the projection of the source data, which in this case is 900913, the projection used in Google Maps, and the default for OpenStreetMap.

If successful, the database will contain the following tables:

planet_osm_line
planet_osm_point
planet_osm_polygon
planet_osm_roads

There are two different tables that contain line data, planet_osm_line and planet_osm_roads . The former includes railroads, subways, and other linear information. The latter is made up exclusively of roads. The planet_osm_point table has a range of data: subway stations, shopping centers, universities, and even brothels. Lastly the planet_osm_polygon table has, but is not limited to, parks, bodies of water, and even buildings in certain urban areas.

After these steps map info can be easily viewed in GIS apps like uDig/ArcView. The GeoServer Web interface also has directs links for KML for previewing the layer in GoogleEarth if installed.

Also today, I had a small rendez-vous with openLayers and I was able to visualize the new OSM data in a Web page. It is quite easy to create and customize, but have a peek in the sources that I used as a guide. I will have to explore this some day later.


J's Stographic Factory

(github페이지가 몇 년째 아직 미완성이라 임시로 블로그에 편하게 기록해두는 중)

OSGEO 교육자료를 우연히 습득해서 이것저것 해보는 중.

오픈소스 GIS서비스를 만들 때에는 DB로 보통 postgreSQL기반의 postGIS를 쓰고, 백단에 geoserver를 올려서 서비스하는 게 일반적인 것 같다.

오늘은 먼저 postgreSQL, postGIS를 설치하고, 일반적으로 공간 데이터로 사용되는 포맷인 shp를 올려 보는 것 까지 해보자.

Introduction to PostGIS Getting Started This workshop uses a data bundle . Download it and extract to a convenient location. Workshop Materials Inside the data bundle, you will find: data/ a directory containing the shapefiles we will be loading All the data in the package is public domain and freel.

오늘 할 일은 여기서 잘 설명해 주고 있다. 링크 안에서 4, 5번이 오늘 할 일에 해당한다.

( 그리고 이 페이지는 그것을 번역만 한 것과 다를 게 없다 )

먼저 postgreSQL을 설치해준다. postgreSQL 역시 오픈소스이다.

New to PostgreSQL? PostgreSQL is a powerful, open source object-relational database system with over 30 years of active development that has earned it a strong reputation for reliability, feature robustness, and performance. There is a wealth of information to be found describing how to in.

살펴 보니 그냥 편하게 binary로 설치해주면 될 것 같다. 아래 링크다.

Please Note: EDB no longer provides PostgreSQL installers for Linux, and users are encouraged to use the platform-native packages. For more information, please see this blog post on Platform Native EDB Packages for Linux Users.

나는 64비트 윈도우 환경에 11.2 최신 버전을 다운로드했다.

pgAdmin은 DB를 관리하고 쿼리를 할 수 있는 GUI환경이다. 같이 설치해 준다.

Stack Builder는 관련된 확장 툴들을 편리하게 설치해 줄 수 있게 해준다.

체크하면 postgreSQL 설치가 끝난 뒤 바로 postGIS를 편하게 설치할 수 있으니 꼭 체크해 주자.

포트넘버, su 비밀번호를 설정하고, Locale은 기본값(Default, System)으로 주고 설치 시작.

Stack Builder를 체크하고 설치했으면, 설치가 끝난 뒤 Stack Builder를 실행하는 옵션이 있다.

체크하고 마치면 Stack Builder가 바로 실행되고, 설치한 서버를 선택하고 다음을 누르면 추가 설치할 수 있는 목록이 나온다. 이 중, 아예 Spatial Extensions가 카테고리로 분류되어 있는 데, 여기 있는 PostGIS 번들을 체크 후 설치하면 된다. (POST GIS + 공간정보 조작에 필수적인 패키지들)

(. ) 뭐가 문제인지 모르겠지만 경로를 셋팅하는 과정에서 자꾸 멈춘다.

진행되면 쭉 설치해 주시고, 안되면 그냥 PostGIS를 따로 찾아서 설치해주자.

Spatial and Geographic objects for PostgreSQL Home Download Documentation Development Support If you need help using these packages (the installers and buildbot binaries), please ask on our users mailing list . If you find a bug with the installers or the buildbot packages, please report these on ou.

나는 64비트 윈도우에 postgreSQL11을 설치했으므로, 맞는 버전을 찾아 설치해준다.

보아하니, postgreSQL11은 postGIS2.5 이후 버전에서만 지원하는 것 같다.

(링크 안에 릴리즈 버전 - OSGEO 다운로드 링크 - pg11폴더 - exe파일을 받아 설치하면 된다.)

여기까지 했으면 필요한 설치는 끝났다. 이제 DB를 만들어 보자.

먼저, postgreSQL을 컨트롤하는 pgAdmin을 실행시킨다.

postgreSQL 11을 클릭해 접속한다. 아까 설치할 때 설정했던 슈퍼유저 비밀번호를 입력하면 된다.

이런저런 속성 정보와 기본 템플릿, 스키마들을 살펴볼 수 있다.

Database 우클릭 -> Create -> Database. 를 클릭해 새로운 DB를 생성한다.

이름은 마음대로 주고, Definition 탭에서 위와 같이 설정을 해 주고 Save를 누르면 새 DB 생성 완료.

이제 postGIS를 새 DB에 장착(?)시킬 시간이다.

postGIS를 장착하면 다음과 같은 쿼리가 가능하다.

위의 쿼리는 city.name이 Gotham인 city에 포함되는 superhero의 superhero.name을 뽑는 SELECT문이다.

즉, 공간적으로 city는 폴리곤일 것이고, superhero는 포인트일 것인 데, 폴리곤 안에 포함되는 포인트를 뽑아내는 것. postGIS는 이렇게 ST_Contains(A, B)와 같은 공간 연산을 가능하게 한다.

새로 만든 DB에 postGIS를 얹으려면, 다음과 같은 쿼리를 입력해 주어야 한다.

쿼리 입력은 대상이 되는 DB에 마우스 오른쪽 - Query tool을 클릭하면 쿼리 창이 나온다.

여기에 쿼리 입력 후 실행(pgAdmin4 기준번개 모양, F5)하면 된다.

쿼리 입력 후 성공적으로 수행되면, Extensions항목에 postgis가 들어간 것을 볼 수 있다.

Schemas>public에서 다양한 부분에 지리(공간)자료를 다룰 수 있는 것들이 추가되었다.

예를 들어 Types를 살펴 보면(기본적으로는 아무것도 없다.) 지오메트리 관련, 래스터 관련 타입들이. Functions에서는 아까 보았던 st_contains를 비롯한 다양한 공간 연산 및 정의 함수들이 있는 것을 볼 수 있다. (1200여개 함수가 정의되어 있다.)

특히 주목해야 할 것은 Tables에서 spatial_ref_sys 테이블이다. 이는 좌표계가 정의되어 있는 테이블이다. 해당 테이블에 마우스 우클릭>View Data를 하거나, 쿼리에

를 입력하면 그 내용을 모두 볼 수 있는 데, 여기서 srid 컬럼이 우리가 일반적으로 알고 있는 EPSG코드이다.

이제 공간 정보를 갖는 데이터를 올려서 조작할 준비가 다 되었다.

이제 부터는 일반적으로 사용하는 shp파일셋을 우리가 만든 DB에 올려보는 시간이다.

이를 위해서 'PostGIS 2.0 Shapefile and DBF Loader Exporter'라는 툴이 필요하다. 이는 postGIS를 설치할 때 번들로 설치되어 왔을 것이다. 못 찾겠으면 postGIS를 설치했던 경로(일반적으로 C:Program FilesPostgreSQL11in)의 postgisgui 내에 shp2pgsql-gui.exe를 실행하면 된다.

실행하면 먼저 DB서버와 연결 셋팅을 해야 한다. View connection details. 를 눌러서 위와 같이 셋팅

(postgres는 기본 계정이고, 5432포트도 기본 포트, Database란에 아까 만든 DB 이름을 넣어준다.)

Add File을 눌러 추가할 shp파일을 올려 준다. 이 때, 좌표계의 EPSG코드를 SRID에 넣어 준다. (더블 클릭으로 편집 가능) 나는 EPSG:5179 좌표계를 가진 일산 주엽역 부근 도로 shp파일을 올려주었다.

혹시 한글 속성 정보가 포함된 파일에서 한글이 깨질 경우 options에서 인코딩을 CP949나 EUC-KR 등으로 바꿔주면 깨짐 없이 읽어올 수 있다.

이후 import를 한다. 혹시 import failed가 뜨면 shp파일셋(dbf포함)이 모두 같은 폴더에 있는 지 확인하고, 그래도 안 되면 디렉토리를 다른 곳으로 옮겨서 시도해 본다.

pgAdmin에서 잘 import되었나 확인해 보자. 바로 database>(DB이름)>Tables를 보면 추가가 안 되어 있는 데,

상단 메뉴에서 Object>Refesh를 한 번 해 주면(새로고침) 같은 경로에 불러온 공간데이터가 잘 포함되어 있다.

참고로 postGIS 테이블은 QGIS에서도 바로 불러다 쓸 수 있다. ( 아래 테이블은 무시하자 )

메뉴에서 postGIS 테이블 추가를 하여 호스트에 localhost로 하여 나머지 항목들을 DB셋팅할 때 사용한 값으로 입력해 주고 연결을 하면 위와 같이 현재 DB에 있는 테이블을 바로 가져올 수 있다.

이 때, SRID를 제대로 설정했으면 불러와서 TMSforkorea를 이용해 웹 지도와 겹쳤을 때 오차 없이 지오메트리가 잘 들어맞을 것이다.


Apache Superset is a Data Visualization and Data Exploration Platform

Superset is fast, lightweight, intuitive, and loaded with options that make it easy for users of all skill sets to explore and visualize their data, from simple line charts to highly detailed geospatial charts. It easily integrates your data, using either our simple no-code viz builder or state of the art SQL IDE. Superset can query data from any SQL-speaking datastore or data engine (e.g. Presto or Athena) that has a Python DB-API driver and a SQLAlchemy dialect.


Register the ST_Geometry column

If you use SQL to create a table that contains an ST_Geometry column, you can register the column to use a specific spatial reference and dimensionality. That way, when you insert records through SQL, you cannot accidentally insert records that use a different spatial reference. To do this, use the sde.st_register_spatial_column function. The syntax for using this function is as follows:

The SRID you specify must exist in the public.sde_spatial_references table. The coordinate dimension indicates whether the data has only x,y coordinates (2), x,y,z coordinates (3), x,y,z,m coordinates (4), or x,y,m coordinates (5). By default, if you do not specify a coordinate dimension, the data is registered as having only x,y dimensions.

In the following example, the shape column of the blocks table in the sasha schema of database mycitydb is registered to use an SRID of 4236 and store only three-dimensional coordinates:

This adds a record for the spatial column to the public.sde_geometry_columns table in the geodatabase or database.

If the spatial column is empty and you register it with a particular SRID and dimensionality, you could unregister it to change the SRID or dimensionality then reregister it with different values. You can unregister a spatial column by executing the st_unregister_spatial_column() function. This function removes the spatial column from the public.sde_geometry_columns system table so the spatial column is no longer associated with any spatial reference system. The syntax for using this function is as follows:

You can check to see whether a spatial column is registered by executing the st_isregistered_spatial_column function. The syntax to use this function is as follows:

If the spatial column is registered with the specified SRID, 1 is returned a 0 is returned if it is not.

To discover what dimensionality the table was registered with, use the st_get_coord_dimension function. The syntax for the st_get_coord_dimension function is as follows:

In this example, st_get_coord_dimension will return xyz since the blocks table is registered as three-dimensional:


Watch the video: PostGreSQL Create PostGIS database and import SHP file. Import Shapefile with PostGIS