DB choice for a geodata application

Question

I'm writing a client-server application where geotagged photos are main content.

Backend is based on ASP.NET Core tech stack.

Backend should be able to search photo in area (by radius), search photo by tag and probably suggest tags (kind of autocomplete), all that stuff will be visible thru some simple REST API.

I'm not really sure if i need relational DB for such a simple task. Also i'd love to see search by distance from point made by DB not by my code. Good integration with .NET Core is a great plus.

I've worked with SQL Server from MSFT, but it seems a bit overweight for this simple task.

Could you guys with some experience in area, advise a DB for my task?

Answer 1

PostgreSQL

Go check out the book PostGIS in Action Second Eddition.

PostGIS has everything you're requesting.

Backend should be able to search photo in area (by radius), search photo by tag and probably suggest tags (kind of autocomplete), all that stuff will be visible thru some simple REST API.

CREATE TABLE photos (
  photoid  serial        PRIMARY KEY,
  path     text,
  tags     text[],
  location geography,
  meta     jsonb
);

INSERT INTO photos(path,tags,location, meta)
VALUES
  (
    '/foo/asdf.jpg',
    ARRAY['person', 'america', 'male'],
    ST_MakePoint(5.493,3.2948),
    '{"CAMERA":"NIKON"}'::jsonb
   );

SELECT *
FROM photos
WHERE tags @> ARRAY['person'];

SELECT *
FROM photos
WHERE ST_DWithin(ST_MakePoint(0,0)::geography, location, 10000);