1

I use postgis database and I download subdistrict shapefile of thailand from:

https://data.humdata.org/dataset/thailand-administrative-boundaries

and I have my point latitude and longitude (13.670524, 100.634237).

I try to query geom column as below query:

SELECT ST_ASTEXT(geom) FROM subdistrict limit 1

The result of geom column are multilinestring not multipolygon and data in multilinestring are coordinates; not lat, long.

enter image description here

I try to open shapefile in QGIS:

enter image description here

How do I query in subdistrict table with my point (13.670524, 100.634237)?

Improve this question
2
  • ...I don't get what you want to achieve with that point of yours. also, you seem to misinterpret what coordinates describe. (multi)lines and (multi)polygins are represented by an ordered sequence of coordinates (can be lat/lon) for their vertices. how did you import your data to PostgreSQL? what CRS did you use, or what's the CRS of the shapefile (I assume EPSG:4326 / WGS84)? how would you like to query your table 'with' your point, e.g. get only the district that holds this point? ...or do you simply want to see it?
    – geozelot
    Commented Jan 30, 2018 at 8:45
  • Sorry, I did not explain enough. I have open district and subdistrict shapefile on QGIS, It difference CRS of District shapefile is EPSG:4326, WGS 84 and geom column is MultiPolygon CRS of Subdistrict shapefile is EPSG:32647, WGS 84 and geom column is Multilinestring I can get district with below query but subdistrict cannot. select * from district where st_contains(geom, st_setsrid(st_makepoint(100.634330, 13.669349), 4326)); I would like to know Query for get subdistrict.
    – Humming Bird
    Commented Jan 31, 2018 at 1:56

1 Answer 1

Reset to default
1

In general, you will need to convert those boundaries to areal representations first to be able to query them for cointaining your point. I opt for ST_Polygonize as it constructs exact (minimal possible) polygons from a collection of (MULTI)LINSESTRINGs, closing any gaps of the input geometries.

I had a quick look at the data; the boundary geometries are rather tricky...
It seems every subdistrict consists of multiple MULTILINESTRINGs, each matching only the border line to another subdistrict. The common attribute for each subdistrict is the fid_tha_ad, while the names for the administrative areas can have duplicates throughout the country.

The following query returns the areal representation, grouped by by fid_tha_ad and the non-thai admin names, that contains your point:

SELECT *
FROM ( 
    SELECT fid_tha_ad,
           prov_name,
           amp_name,
           tam_name,
           (ST_Dump(ST_Polygonize(geom))).geom AS geom
    FROM <your_boundary_table_name>
    GROUP BY fid_tha_ad,
             prov_name,
             amp_name,
             tam_name
) AS area
WHERE ST_Contains(
          area.geom,
          st_transform(
              st_setsrid(
                  st_makepoint(100.634330, 13.669349),
                  4326
              ),
              32647
          )
       )

You can add/replace the necessary column names both in the SELECT and GROUP BY clauses of the subquery. If you want to refer your original data to the results, use the fid_tha_ad column.

Hope that helps?

Improve this answer
2

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.