File size: 6,438 Bytes
3a66909 90d6ea3 70c9a8f 4263e68 e0e5b16 b32ebc4 3a66909 4263e68 3a66909 4263e68 b32ebc4 4263e68 e0e5b16 4263e68 e0e5b16 4263e68 e0e5b16 4263e68 e0e5b16 4263e68 e0e5b16 4263e68 e0e5b16 4263e68 |
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 169 170 171 172 173 174 175 176 177 178 179 180 181 182 183 184 185 186 187 188 189 190 191 192 193 194 195 196 197 198 199 200 201 202 |
---
license: llama3
base_model: meta-llama/Meta-Llama-3.1-8B
language:
- en
- zh
- es
pipeline_tag: text-generation
---
### Model Information
This model, Llama-3.1-8B-Instruct-Spatial-SQL-1.0, is an 8B, 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
**Associated code**: https://github.com/mprodrigo - coming soon
**Model Architecture**: The model is a QLoRA / Supervised Fine Tuning (SFT)
### Model Input / Output Overview:
Input: Text plus coordinate prompt injection.
Output: **PostGIS spatial SQL**
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>
\### Input: ¿Cuál es el área para el polígono? : 'Polygon ((-3.7515154 40.3855551, -3.7514972 40.3856581, -3.7507005 40.3855767, -3.7507167 40.3854722, -3.7515154 40.3855551))'
<p></p>
\### Input: 多邊形的面積是多少？ : '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>
\### Input: ¿Cuál es el centroide del polígono? : 'Polygon ((-3.6934636 40.4808785, -3.6933352 40.4811486, -3.6930125 40.4810598, -3.693141 40.4807897, -3.6934636 40.4808785))'
<p></p>
### Input: 多邊形的質心是什麼？ : '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;
<p></p>
BUFFER
<p></p>
\### Input: What is the thousand meter buffer for the following point? : 'Point(-8.7522658 41.3862664)'
<p></p>
\### Input: ¿Cuál es el buffer de mil metros para lo siguiente punto? : 'Point(-8.7522658 41.3862664)'
<p></p>
\### Input: 以下點的千米緩衝區是多少？ : '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>
\### Input: ¿Cuánto dura la línea? : 'LINESTRING (-3.6976693 40.4263178, -3.6986082 40.4258729)'
<p></p>
\### Input: 隊伍有多長？ : '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
<p></p>
temp ~ .4-.5 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: 2e-06
- train_batch_size: 1
- eval_batch_size: 1
- distributed_type: multi-GPU
- num_devices: 2
- total_train_batch_size: 100
- total_eval_batch_size: 10
- optimizer: Adam 8bit
- lr_scheduler_type: linear
- lr_scheduler_warmup_steps: 10
- num_epochs: 3
### Training results
| Training Loss | Epoch | Step | Validation Loss |
|:-------------:|:------:|:----:|:---------------:|
| 0.5438 | 1 | 10 | 0.5247 |
| 0.4889 | 2 | 20 | 0.4494 |
| 0.4072 | 3 | 30 | 0.4051 |
### Framework versions
- Transformers 4.44.0
- Pytorch 2.4.0
- peft 0.12.0
- Datasets 2.21.0
- Tokenizers 0.19.1 |