Creating a large virtual table in FROM clause

Question

Background I have a Java application which periodically takes a resultset from one database (MySQL), and attempts to find matches in another database (Postgres / PostGIS to be specific). Problem Presently, the application queries the Postgres database once for ever MySQL record in the result set (could be upwards of tens of thousands). I'm attempting to alter the algorithm so that the application generates one single query that yields multiple results if any matches are found. Another way to describe my goal is that the query should behave similar to a typical JOIN, if these two tables existed within the same database system. Current Solution In order to solve this, I'm creating a virtual table in the `FROM` clause. However, the only way I know how to do this from a list of values is by writing individual `SELECT` statements joined with a `UNION`. The result appears to work, and while I haven't tested for performance with thousands of records, it doesn't appear to have any massive imp…

Accepted Answer

Here is a better way to create a table of values on the fly

```
select *
from (
  values
    (1100::int, 'POINT(-81.19701 32.09279)'::geography(Point)),
    (1100::int, 'POINT(-81.19682 32.09224)'::geography(Point))
) as t(unit_id, geom)
```

Yet even better idea is perhaps to use foreign data wrapper to bring your mysql table in PG.

EDIT

You might want to try to pre-screen records from your main table provided it is indexed with `ST_Dwithin(latlng, geom, 0.1)` provided that 0.1° is just slightly more (mind cos(32.09)) than 10000 m.