2

I have a table called city, which currently only contains the following columns:

  1. id
  2. name
  3. lat (for center point)
  4. long (for center point)
  5. date_created

I would like to store a GeoJSON response like this one here

My question is, which datatype should i use to store it ?

Things to be considered

  1. Later on i would like to be able to query like this: given a x,y coord i would like to know in which city such coord belongs to (or inside of)
  2. I am using Postgres, AWS - RDS (latest version)
  3. Don't have postGIS installed

Some solution that i found online:

  1. jsonb << i am not sure if i use this data type i would be able to query
  2. ST_AsGeoJSON << according to this, i can use such function however i don't have postGIS installed or does this function available out of the box

If anyone could suggest me which datatype i should use, along with how should i use it in my query that would be very helpful. I really appreciate your input. Thanks

Jeremy
  • 2,516
  • 8
  • 46
  • 80
  • I believe JSONB can be queried. See https://stackoverflow.com/a/33731703/784829 which has examples of querying it. – Lomky Aug 01 '18 at 16:57
  • Is it possible to instal postGIS? If not you cannot use functions for this package. If it not posssible then you can stor iat as standrd JSON or JSONB – Grzegorz Grabek Aug 03 '18 at 10:54
  • it doesn't *really* matter much what datatype you are going for (with *jsonb* being the obvious choice), without any specialized framework to process/transform geographic coordinates, your final task will reach it's limits pretty soon. you'd be stuck with implementig the geodetic vector math for 'point within polygon' yourself. you could try and look into PostgreSQL's own (simple, cartesian) geometry types and functions, but conversion is tedious and error prone. – geozelot Aug 04 '18 at 11:16
  • Thank you so much for all the responses, i've decided to just install postGIS instead. Since it will benefit in the long run. – Jeremy Sep 04 '18 at 13:27

1 Answers1

0

Thanks for all the responses, after more googling i've decided to install postGIS and make adjustment since this is more beneficial in the long run.

Thanks all.

Jeremy
  • 2,516
  • 8
  • 46
  • 80