|
--- |
|
license: llama3.2 |
|
base_model: meta-llama/Meta-Llama-3.2-1B |
|
language: |
|
- en |
|
pipeline_tag: text-generation |
|
tags: |
|
- code |
|
- spatial |
|
- sql |
|
- GIS |
|
- PostGIS |
|
--- |
|
|
|
**ENGLISH ONLY - Use 8b models for alternate languages.** |
|
|
|
### Model Information |
|
|
|
This model, Llama-3.2-1B-Instruct-Spatial-SQL-1.0, is an 1B, narrow use case, text to spatial SQL, lightly fine-tuned model. In general, its primary use case |
|
is the Natural Language command adaptation of particular geographic spatial functions as normally defined in pure SQL. Data input should be a combination of an English prefix in the form of a question, and a coordinate prompt injection, likely from an active mapping system application coordinate list. Output is PostGIS spatial SQL. |
|
|
|
There are four primary geographic functions released in version 1.0. |
|
|
|
**Model developer**: Mark Rodrigo |
|
|
|
**Github**: https://github.com/mprodrigo/spatialsql |
|
|
|
**Model Architecture**: The model is a QLoRA / Supervised Fine Tuning (SFT) |
|
|
|
### Model Input / Output Overview: |
|
|
|
Input: Text plus coordinate prompt injection. |
|
</br> |
|
Output: **PostGIS spatial SQL** |
|
</br> |
|
NOTE: Inputs and outputs are in meters and or geographic decimal degrees WGS 84 coordinates. |
|
|
|
| Function | Question Input | Geo Input | SQL Execution Output | |
|
|:---------:|:---------------:|:---------:|:-------------------------:| |
|
| Area | Area question | Polygon | Number - Area sq meters | |
|
| Centroid | Center question | Polygon | Point | |
|
| Buffer | Buffer distance | Point | Polygon | |
|
| Length | Length question | Line | Number - Length in meters | |
|
|
|
### Example Prompt / Prompt File |
|
|
|
<|begin_of_text|><|start_header_id|>system<|end_header_id|> |
|
<p></p> |
|
You are a helpful assistant. You are an expert at PostGIS and Postgresql and SQL and psql. |
|
<p></p> |
|
<|eot_id|><|start_header_id|>user<|end_header_id|> |
|
|
|
\### Instruction: Write a PostGIS SQL statement for the following. |
|
<p></p> |
|
|
|
\### Input: |
|
|
|
<p></p> |
|
{input} |
|
|
|
<p></p> |
|
|
|
\### Response: |
|
|
|
<|eot_id|><|start_header_id|>assistant<|end_header_id|> |
|
|
|
|
|
### Examples |
|
|
|
|
|
AREA |
|
<p></p> |
|
|
|
\### Input: What is the area for the polygon? : 'Polygon ((-3.7515154 40.3855551, -3.7514972 40.3856581, -3.7507005 40.3855767, -3.7507167 40.3854722, -3.7515154 40.3855551))' |
|
<p></p> |
|
|
|
\### Response: SELECT ST_Area(geog) As area FROM (select 'Polygon ((-3.7515154 40.3855551, -3.7514972 40.3856581, -3.7507005 40.3855767, -3.7507167 40.3854722, -3.7515154 40.3855551))' :: geography geog) subquery; |
|
<p></p> |
|
|
|
CENTROID |
|
<p></p> |
|
|
|
\### Input: What is the centroid for the polygon? : 'Polygon ((-3.6934636 40.4808785, -3.6933352 40.4811486, -3.6930125 40.4810598, -3.693141 40.4807897, -3.6934636 40.4808785))' |
|
<p></p> |
|
|
|
\### Response: SELECT ST_AsText(ST_Centroid(geog)) As centroid FROM (select 'Polygon ((-3.6934636 40.4808785, -3.6933352 40.4811486, -3.6930125 40.4810598, -3.693141 40.4807897, -3.6934636 40.4808785))' :: geography geog) subquery; |
|
|
|
BUFFER |
|
<p></p> |
|
|
|
\### Input: What is the thousand meter buffer for the following point? : 'Point(-8.7522658 41.3862664)' |
|
<p></p> |
|
|
|
\### Response: SELECT ST_AsText(ST_Buffer(geog, 1000)) as buffer FROM (select 'Point(-8.7522658 41.3862664)' :: geography geog) subquery; |
|
|
|
<p></p> |
|
LENGTH |
|
<p></p> |
|
|
|
\### Input: How long is the line? : 'LINESTRING (-3.6976693 40.4263178, -3.6986082 40.4258729)' |
|
<p></p> |
|
|
|
\### Response: SELECT ST_Length(geog) As length FROM (select 'LINESTRING (-3.6976693 40.4263178, -3.6986082 40.4258729)' :: geography geog) subquery; |
|
<p></p> |
|
|
|
### A Few Known Question Variation Examples |
|
|
|
<p></p> |
|
AREA |
|
<p></p> |
|
What is the area for the geometry? |
|
<p></p> |
|
What is the area for this polygon? |
|
<p></p> |
|
CENTROID |
|
<p></p> |
|
What is the centroid for the geometry? |
|
<p></p> |
|
What is the center point of the polygon? |
|
<p></p> |
|
BUFFER |
|
<p></p> |
|
What is the 100 meter buffer for the following point? |
|
<p></p> |
|
Buffer the following point a thousand meters. |
|
<p></p> |
|
What is the 1000 meter buffer for the following point? |
|
<p></p> |
|
LENGTH |
|
<p></p> |
|
What is the length of the line? |
|
<p></p> |
|
How long is this line? |
|
|
|
|
|
### llama.cpp / Hyperparameter Recommendations For Inference |
|
max context ~ 8,000 or lower |
|
<p></p> |
|
top k ~ 100 or lower |
|
<p></p> |
|
temp ~ .4-.7 or lower |
|
|
|
### Agent Considerations |
|
Agents are being considered as a separate project. Agents would mostly be related to pulling the coordinates from a mapping UI, and executing the SQL from responses against a PostGIS database. |
|
|
|
### Further Reference - link this |
|
https://postgis.net/docs/manual-3.3/PostGIS_Special_Functions_Index.html#PostGIS_GeographyFunctions |
|
|
|
### Evaluation data |
|
More information needed |
|
|
|
### Training data |
|
Custom synthetic |
|
|
|
### Training hyperparameters |
|
|
|
The following hyperparameters were used during training: |
|
- learning_rate: 3e-05 |
|
- train_batch_size: 10 |
|
- eval_batch_size: 3 |
|
- distributed_type: multi-GPU |
|
- num_devices: 2 |
|
- optimizer: Adam 8bit |
|
- lr_scheduler_type: linear |
|
- num_epochs: 20 |
|
|
|
### Training results |
|
|
|
| Training Loss | Epoch | Step | Validation Loss | |
|
|:-------------:|:------:|:----:|:---------------:| |
|
| 1.9106 | 20 | 10 | 1.9183 | |
|
| 1.1674 | 20 | 20 | 1.1710 | |
|
| 0.7446 | 20 | 30 | 0.7589 | |
|
| 0.6143 | 20 | 40 | 0.6220 | |
|
| 0.5773 | 20 | 50 | 0.5695 | |
|
| 0.5328 | 20 | 60 | 0.5334 | |
|
| 0.5146 | 20 | 70 | 0.5096 | |
|
| 0.4895 | 20 | 80 | 0.4925 | |
|
| 0.4893 | 20 | 90 | 0.4772 | |
|
| 0.4710 | 20 | 100 | 0.4612 | |
|
| 0.4572 | 20 | 110 | 0.4438 | |
|
| 0.4358 | 20 | 120 | 0.4233 | |
|
| 0.4002 | 20 | 130 | 0.4014 | |
|
| 0.3812 | 20 | 140 | 0.3768 | |
|
| 0.3461 | 20 | 150 | 0.3492 | |
|
| 0.3222 | 20 | 160 | 0.3202 | |
|
| 0.3043 | 20 | 170 | 0.2921 | |
|
| 0.2727 | 20 | 180 | 0.2651 | |
|
| 0.2378 | 20 | 190 | 0.2403 | |
|
| 0.2229 | 20 | 200 | 0.2184 | |
|
| 0.2121 | 20 | 210 | 0.1990 | |
|
| 0.1891 | 20 | 220 | 0.1834 | |
|
| 0.1867 | 20 | 230 | 0.1723 | |
|
| 0.1848 | 20 | 240 | 0.1654 | |
|
| 0.1732 | 20 | 250 | 0.1631 | |
|
|
|
|
|
|
|
|
|
### Framework versions |
|
|
|
- Transformers 4.45.1 |
|
- Pytorch 2.5.0 |
|
- peft 0.13.2 |
|
- Datasets 3.0.1 |
|
- Tokenizers 0.20.1 |
