Search code examples
sqlpostgresqlspring-bootpostgisshapefile

How to query data from PostGIS by country?

I've download the a world map shapefile from TM_WORLD_BORDERS_SIMPL-0.3.zip

The world map table's columns are as below:

gid integer NOT NULL DEFAULT nextval('table_world_gid_seq'::regclass),
fips character varying(2) COLLATE pg_catalog."default",
iso2 character varying(2) COLLATE pg_catalog."default",
iso3 character varying(3) COLLATE pg_catalog."default",
un smallint,
name character varying(50) COLLATE pg_catalog."default",
area integer,
pop2005 bigint,
region smallint,
subregion smallint,
lon double precision,
lat double precision,
geom geometry(MultiPolygon,4326)

Now I want to query my data by the geom column, for example, query data in China.

//function in controller
@GetMapping("china")
fun getInChina(): Result<List<WalkItem>> {
    val ce = countryRepo.findById(30).get()
    val wes = walkRepo.findInCountry(ce.geom!!)
    return Result.success(wes)
}

//function in repository
@Query("SELECT new tech.return0er.donkeygo.model.WalkItem(w) FROM ${WalkEntity.TABLE_NAME} w WHERE ST_Intersects(ST_PointOnSurface(:area), w.start_point) = true")
fun findInCountry(area: Geometry): List<WalkItem>

But always return empty data list. I do have data in China area.

PS: I have another query by get map's top-left corner position and bottom-right corner position.

@Query("SELECT new tech.return0er.donkeygo.model.WalkItem(w, u) FROM ${WalkEntity.TABLE_NAME} w, ${UserEntity.TABLE_NAME} u WHERE INTERSECTS(w.start_point, :area) = true" +
            " AND w.visibility=${WalkEntity.VISIBLE_TO_ALL} AND w.state=${WalkEntity.STATE_VISIBLE}" +
            " AND w.uid=u.uid" +
            " ORDER BY w.start_time DESC")
fun findWithin(area: Geometry, pageable: Pageable): List<WalkItem>

@GetMapping("within")
fun findWithin(@RequestParam("lat_north") latNorth: Double,
               @RequestParam("lon_west") lonWest: Double,
               @RequestParam("lat_south") latSouth: Double,
               @RequestParam("lon_east") lonEast: Double,
               page: Int, size: Int): Result<List<WalkItem>> {
    val rectangle = GeometryFactory().createPolygon(arrayOf(
            Coordinate(latNorth, lonWest),
            Coordinate(latNorth, lonEast),
            Coordinate(latSouth, lonEast),
            Coordinate(latSouth, lonWest),
            Coordinate(latNorth, lonWest)
    ))
    val wes = walkRepo.findWithin(rectangle, PageRequest.of(page, size))
    return Result.success(wes)
}

This works find. I tried to pass China's gemo to function findWithin's area parameter, but also query nothing out.

Some of my data:

{
    "altitude": 1391.2064208984375,
    "latitude": 39.99987095,
    "longitude": 115.43765119
}
{
    "altitude": 45.00225830078125,
    "latitude": 40.00929495,
    "longitude": 116.38653411
}
{
    "altitude": 1075.603759765625,
    "latitude": 39.86346926,
    "longitude": 115.59797164
}

These are some of the points that I want to query in China.

The screenshot picture shows data that I query out by map's corner position. Query by map's corner positions

Solution

  • I believe you're querying latitude longitude instead of longitude latitude - the latter combination is the most common among all SRS. Your coordinates look just fine and they do overlap with the polygon of China from your data set.

    Query - importing the shapefile and the coordinates you provided

    WITH j AS (
  VALUES ('SRID=4326;POINT(115.43765119 39.99987095)'::GEOMETRY),
         ('SRID=4326;POINT(116.38653411 40.00929495)'::GEOMETRY),
         ('SRID=4326;POINT(115.59797164 39.86346926)'::GEOMETRY)
)
SELECT ST_AsText(column1), name FROM j, table_world
WHERE ST_Contains(geom,column1);

---------------------------------+-------
 POINT(115.43765119 39.99987095) | China
 POINT(116.38653411 40.00929495) | China
 POINT(115.59797164 39.86346926) | China

(3 Zeilen)