How to query Postgresql+openstreetmap

先行输入：psql gis

\d  显示当前数据表

               List of relations Schema |        Name        | Type  |  Owner   --------+--------------------+-------+---------- public | geography_columns  | view  | postgres public | geometry_columns   | table | gis public | planet_osm_line    | table | user public | planet_osm_nodes   | table | user public | planet_osm_point   | table | user public | planet_osm_polygon | table | user public | planet_osm_rels    | table | user public | planet_osm_roads   | table | user public | planet_osm_ways    | table | user public | spatial_ref_sys    | table | gis(10 rows)

To enter a data table, the statement is: \d planet_osm_roads

就会出现如下的显示：

     Table "public.planet_osm_roads"       Column       |   Type   | Modifiers --------------------+----------+----------- osm_id             | bigint   |  access             | text     |  addr:housename     | text     |  addr:housenumber   | text     |  addr:interpolation | text     |  admin_level        | text     |  aerialway          | text     |  aeroway            | text     |  amenity            | text     |  .... waterway           | text     |  wetland            | text     |  width              | text     |  wood               | text     |  z_order            | integer  |  way_area           | real     |  way                | geometry |

You can then access the operation, as follows:

SELECT Osm_id,name,way from planet_osm_roads LIMIT 1;

However, later found in Python, PSYCOPG2 does not seem to support the input of \d, so I later changed a method of querying:

Select Planet_osm_roads.\ "osm_id\", St_astext (planet_osm_roads.\ "the_geom\") from Public.planet_osm_roads;

This statement is a bit more formal, but it can be cumbersome to use and need to go from the top to the database, but the query results are definitely the same.

In addition, because the THE_GEOM in OpenStreetMap is stored by the geometry data type, I use the St_astext function for format conversion.

After the format is converted, it appears as:

(' LINESTRING (12936024.28 4839648.07,12935424.71 4838722.06,12934892.21 4837629.71,12934289.2 4836007.5,12933955.4 4834815.49,12933523.99 4834253.51,12932398.66 4833859.51,12930942.93 4833698.73,12930017.2 4833337.34,12928792.52 4832545.6,12927333.4 4830992.71,12926605.54 4830332.04,12925976.25 4830134.83,12925216.94 4830137.07,12924226.69 4830471.27,12923535.34 4830870.63,12923537.07 4831566.67,12923670.37 4832229,12924035.56 4832857.78,12924368.51 4833585.97,12924703.14 4834446.89,12923880.16 4835708.54,12922724.27 4836241.84,12922132.27 4836939.92,12921903.85 4838034.36,12921642.24 4839195.88,12921447.77 4840290.58,12921153.05 4841352.77,12920474.79 4841645.26,12918234.82 4842608.73) ')

It should represent the GPS coordinate values of each node.

Also attached is the function set of ST:

Gets the wkt description of the Geometry object St_astext (geometry)
Gets the WKB description of the Geometry object St_asbinary (geometry)
Gets the spatial reference ID of the Geometry object St_srid (geometry)
Gets the dimension of the Geometry st_dimension (geometry)
Gets the bounding range of the geometric object St_envelope (geometry)
Determines whether a geometry object is empty st_isempty (geometry)
Determine if a Geometry object does not contain a special point (such as self-intersecting) st_issimple (geometry)
Determine if a Geometry object is closed st_isclosed (geometry)
Determines whether the curve is closed and does not contain a special point st_isring (geometry)
Gets the number of objects in a multi-geometry object St_numgeometries (geometry)
Gets the Nth object St_geometryn (Geometry,int) in a multi-geometry object
Gets the number of points in the geometric object St_numpoints (geometry)
Gets the nth point of a geometric object St_pointn (Geometry,integer)
Gets the outer edge of the polygon st_exteriorring (geometry)
Gets the number of boundaries within a polygon st_numinteriorrings (geometry)
Ibid. st_numinteriorring (geometry)
Gets the nth inner boundary of a polygon st_interiorringn (Geometry,integer)
Get the end of the line St_endpoint (geometry)
Gets the starting point of the line St_startpoint (geometry)
Gets the type of Geometry object Geometrytype (geometry)
Similarly, but does not check the M-value, that is, the Pointm object is judged to be point st_geometrytype (geometry)
Gets the X-coordinate of the point st_x (geometry)
Gets the Y-coordinate of the point st_y (geometry)
Gets the Z-coordinate of the point st_z (geometry)
Gets the M-value of the point st_m (geometry)