![]() ![]() Mountains=# SELECT ST_Distance(a.location, b.location)/1000 as Dist_deg from ski_resorts a, ski_resorts b where a.name='Breckenridge' AND b.name='Vail' Ĭreate and query a 3D Georaphic database: For example, find the distance (in km) from Breckenridge to Vail (as the crow fies): GIS queries do more than just retrieve data but perform GIS analysis. Mountains=# SELECT ST_X(ST_AsText(location)), ST_Y(ST_AsText(location)) AS Dist_deg FROM ski_resorts WHERE name='Vail' Mountains=# SELECT ST_AsText(location) AS Dist_deg FROM ski_resorts WHERE name='Vail' GIS query for location as well as for the individual longitude/latitude values in degrees: Note that it is "longitude" space "latitude"! Latitudes outside the range of -90 to 90 degrees will cause an error. " gives the following error:ĮRROR: parse error - invalid geometry You may have the order mixed up. CHARACTER(2), country_code CHARACTER(2), location GEOGRAPHY(.Ĭreate and connect to the database first and then " CREATE EXTENSION postgis " : the command " CREATE TABLE ski_resorts. : the command " CREATE EXTENSION postgis " gives the following error:ĮRROR: could not open extension control file "/usr/share/postgresql/9.3/extension/ntrol": No such file or directory Install missing package: apt-get install postgresql-9.3-postgis-scripts This is important when designing a PostGIS/PostgreSQL database schema. This is enforced by the PRIMARY KEY constraint. Tools like QGIS require the inclusion of an integer field with unique values. Note the "id" integer field identified as a PRIMARY KEY. Mountains=# GRANT ALL PRIVILEGES ON ski_resorts to dude Mountains=# CREATE USER dude WITH PASSWORD 'supersecret' ![]() Id | name | state_code | country_code | location Mountains=# CREATE TABLE ski_resorts (id INTEGER NOT NULL, name VARCHAR(30) NOT NULL, state_code CHARACTER(2), country_code CHARACTER(2), location GEOGRAPHY(POINT,4326), CONSTRAINT pk_id PRIMARY KEY(id)) Postgres=# CREATE EXTENSION postgis_topology VALUES (9, 'Allen', 25, 'Texas', 15000.You are now connected to database "mountains" as user "postgres". INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY) ![]() Let us add two more records to this table as follows − SELECT DISTINCT column1, lumnNĬonsider the table COMPANY having records as follows − ![]() The basic syntax of DISTINCT keyword to eliminate duplicate records is as follows − While fetching such records, it makes more sense to fetch only unique records instead of fetching duplicate records. There may be a situation when you have multiple duplicate records in a table. The PostgreSQL DISTINCT keyword is used in conjunction with SELECT statement to eliminate all the duplicate records and fetching only unique records. ![]()
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |